Выиграет ли нетекстовый поиск от поисковой системы?

У меня есть сайт с поиском. Запуск базы данных MySQL. Интересно, выиграет ли он в производительности от поисковика (Sphinx, Lucene и т.д.)? Как, если будет? Могу ли я использовать фасетный поиск? Я знаю, было бы лучше, если бы был текстовый поиск. Но может ли это принести пользу, если большинство запросов выглядят примерно так:

select SQL_CALC_FOUND_ROWS distinct tableA.id
    from tableA as A
        join tableB as B1 on A.id=B1.tablea_id
        join tableB as B2 on A.id=B2.tablea_id
        join tableB as B3 on A.id=B3.tablea_id
where
    B1.value in ([list of ints here])
and
    B2.value in ([another list of ints here])
and
    B2.value in ([one more list of ints here])
order by ~A.updated_at
limit <from>,<amount>;

Идея состоит в том, чтобы искать строки в tableA со значениями в tableB из первого списка, затем фильтровать, чтобы оставить те, которые имеют значения в tableB из второго списка и т. д. Сортировать их, подсчитывать все найденные и ограничивать.

tableA и tableB такие:

create table tableA (
    id int(11) not null autoincrement,
    ...
    updated_at timestamp not null,
    primary key (`id`),
    key `ix_tablea_updated_at` (`updated_at`)
) engine=InnoDB;

create table tableB (
    tablea_id int(11) not null,
    value int(11) not null,
    key `ix_tableb_tablea_id` (`tablea_id`),
    key `ix_tableb_value` (`value`)
) engine=InnoDB;

tableA содержит ~200 тыс. строк. tableB содержит ~1,2 млн строк. Количество B.value in ([list of ints]) отличается от запроса к запросу, как и lists of ints.

Если я не могу получить выгоду от поисковой системы, могу ли я улучшить производительность любым другим способом?

Насколько я могу сказать, проблема в order by ~A.updated_at и подсчете найденных строк. Есть ли способ ускорить сортировку и подсчет с помощью самого MySQL?

PS. Извините за мой английский. Надеюсь, вы меня поймете.


mysql search lucene faceted-search sphinx
person zaquest    schedule 21.07.2013    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Почему вы присоединяетесь к таблице B три раза по одному и тому же идентификатору? Вы можете получить тот же эффект с помощью одного соединения:

select SQL_CALC_FOUND_ROWS distinct tableA.id
from tableA A join
     tableB B
     on A.id = B.tablea_id
where B.value in ([list of ints here]) and
      B.value in ([another list of ints here]) and
      B.value in ([one more list of ints here])
order by A.updated_at
limit <from>, <amount>;

Наличие трех списков является излишним, поэтому вы также можете сделать:

select SQL_CALC_FOUND_ROWS distinct tableA.id
from tableA A join
     tableB B
     on A.id = B.tablea_id
where B.value in ([big big combined list of ints here]) 
order by A.updated_at
limit <from>, <amount>;

Если у вас есть индекс B(value) или даже B(value, tablea_id), производительность будет еще лучше.

РЕДАКТИРОВАТЬ:

Нет, ваш запрос работает не так, как вы думаете. Каждый раз, когда вы присоединяетесь к таблице be, вы умножаете количество строк. Скажем, значению QQQ в таблице A соответствует 10 строк в таблице B. Первое соединение получает 10 строк, второе умножает это число на 100, а третье — на 1000. Это, вероятно, корень вашей проблемы с производительностью.

Вы просто выполняете последовательную фильтрацию в одном и том же столбце. На самом деле, я подозреваю, что вы действительно хотите знать все А, где в каждом из трех списков есть идентификатор Б. Если это так, то это запрос «набор внутри наборов», и его легко выполнить с помощью group by:

select SQL_CALC_FOUND_ROWS tableA.id
from tableA A join
     tableB B
     on A.id = B.tablea_id
group by tableA.id
having sum(B.value in ([list of ints here])) > 0 and
       sum(B.value in ([another list of ints here])) > 0 and
       sum(B.value in ([one more list of ints here])) > 0
order by A.updated_at
limit <from>, <amount>;

Ваш оригинальный подход, вероятно, работает, что интересно. Обычно это было бы довольно неэффективно (если только одно из значений никогда не появляется в данных, поэтому соединения в конечном итоге не возвращают строк).

person Gordon Linoff    schedule 21.07.2013
comment
Если я присоединюсь к tableB только один раз, то B.value должен быть во всех 3-х списках одновременно. (Не должно ли?). И если я присоединюсь к нему несколько раз, я могу отфильтровать A, чтобы получить B.values ​​из первого списка, из второго списка и т. Д., Отдельно. Я ошибаюсь? Кажется, это работает, как я говорю. У меня есть индекс ix_tableb_value. - person zaquest; 21.07.2013
comment
Я не фильтрую по тому же столбцу. Если у меня есть для некоторой строки в A (1,2,3) значения из B, то объединение B 3 раза даст мне [(1,1,1), (1,1,2), (1,1,3 ),(1,2,1),..,(3,3,3)], а затем я могу найти A, где B1.value=1, B2.value=2, B3.value=3. Это правильно? - person zaquest; 22.07.2013
comment
Использование group by дает те же результаты, но выполняется примерно в 2 раза дольше. В любом случае, спасибо за попытку. - person zaquest; 22.07.2013
comment
@zaquest . . . Если вы удалите таблицу A (и order by и замените group by на tablea_id), сколько времени займет запрос с group by? - person Gordon Linoff; 22.07.2013
comment
Запрос из моего вопроса занимает 4-5 секунд; от вашего ответа 9-10с; group by без order by и без А занимает 5-6с. Если я удалю SQL_CALC_FOUND_ROWS и order by, все они вернут ответ мгновенно (0,00 с) - person zaquest; 22.07.2013
comment
Есть основания полагать, что SQL_CALC_FOUND_ROWS сам по себе плохой. Вы можете обнаружить, что формируете свой запрос как два отдельных запроса: один для получения «количества» фактических строк, а другой — с использованием COUNT(...) для получения общего количества строк. - person barryhunter; 22.07.2013
comment
@barryhunter Да, я слышал, что SQL_CALC_FOUND_ROWS может быть медленным. Я тестировал оба способа с count() и с SQL_CALC_FOUND_ROWS. В моем случае нет никакой разницы. Пока присутствует один из order by или SQL_CALC_FOUND_ROWS, запрос выполняется медленно. И на самом деле count() и order by в отдельных запросах занимают в два раза больше времени, чем SQL_CALC_FOUND_ROWS и order by в одном запросе. - person zaquest; 22.07.2013