Оптимизация UNION

denisimus77

Новичок
Оптимизация UNION

Есть такой запрос

(SELECT SQL_CALC_FOUND_ROWS MATCH(pl.desc) AGAINST("text") AS score, p.post_date, pl.post_name, p.img_id, p.cat_id
FROM posts_text_lang1 AS pl, posts_text1 AS p WHERE pl.lang = "en" AND MATCH(pl.desc) AGAINST("text") AND pl.rel_id = p.id )
UNION ALL
(SELECT MATCH(pl.desc) AGAINST("text") AS score, p.post_date, pl.post_name, p.img_id, p.cat_id
FROM posts_text_lang2 AS pl, posts_text2 AS p WHERE pl.lang = "en" AND MATCH(pl.desc) AGAINST("text") AND pl.rel_id = p.id )
UNION ALL
(SELECT MATCH(pl.desc) AGAINST("text") AS score, p.post_date, pl.post_name, p.img_id, p.cat_id
FROM posts_text_lang3 AS pl, posts_text3 AS p WHERE pl.lang = "en" AND MATCH(pl.desc) AGAINST("text") AND pl.rel_id = p.id )
ORDER BY score DESC LIMIT 0, 20

Происходит полнотекстовый поиск по трем таблицам, затем результаты всех таблиц объеденяются, сортируются по коэфициенту релевантности и выводятся порциями по 20 записей на страницу.
Внутри каждого запроса две таблицы, одна главная posts_text1, другая posts_text_lang1 содержит перводы постов на разные языки. К сути вопроса я думаю они мало имеют отношения.

С каждым постом связаны еще каритнки и категория, в p.img_id, p.cat_id хранятся индексы соответствующих
записей. И на странице вывода нужно эту картинку и категорию показать.

Задача написать запрос оптимальным образом.

Я не знаю как лучше извлекать значения для картинки и имени категории, включать эту выборку в внутрь каждого UNIONа
или потом отдельно пробижаться по выбранным 20 строчкам и добавить к ним необходимую информацию.
Меня смущает то, что Каждый SELECT внутри UNIONа, охватывает очень большие таблицы и результат поиска может возвращать тысячи строк, и как я понимаю MySQL должен выбрать каждую из трех таблиц польностью, потом все сложить и отсортировать. Это так?
Если да, то наверно будет очень накладно к этой операции добавлять еще несколько объединений с др. таблицами?

Я думал все дополнительные выборки сделать после полнотекстового поиска, пробигая по выбранным строкам циклом и создавая таким образом еще 20 запросов к БД, но они должны быть быстрые, т.к. будет использоваться PRIMARY KEY, для выборки.

Еще вопрос, как повлияет на производителность, если для каждого SELECTа добавить свой ORDER BY score, это может ускорить последующую общую сортировку или только хуже?

Спасибо.
 

camka

не самка
По умолчанию при полнотекстовой выборке (если это не boolean mode) mysql уже сам сортирует результат по релевантности. Поэтому достаточно будет извлечь по 20 первых записей из каждой таблицы, не сортируя, и уже потом отсортировать эти 60 записей по релевантности и показать первые 20.

Однако, это только для первой выборки в 20 элементов. С постраничным выводом будет сложнее.

(Предполагается что индекс на pl.rel_id и полнотекстовой индекс на pl.desc установлены.) Возможно, поможет составной индекс (p.rel_id, p.lang).

-~{}~ 22.03.07 09:56:

сколько записей в таблицах?
 

akd

dive now, work later
Команда форума
а это реальный запрос или "высосаный"?

если реальный, то зачем все эти 1,2,3 и не пора-ли переделать структуру?
 

denisimus77

Новичок
Запрос этот упрощенный в действительности он гораздо навороченее и объединяет не три таблицы, а 4, скоро будет 5.
Здесь я его привел чтобы показать суть.
Структуру не изменить, слить их в одну не получится или как-то переделать тоже не получится.

Результат вывода всегда постраничный, т.е. пользователь листает страницы вывода.

У меня конкретный вопрос, касающийся работы UNION.
Стоит ли мне включать в каждый SELECT, который внутри UNION, выборку из всех сопутствующих таблиц: категория, картинки и пр. что необходимо.
Или это сделать после выборки нужной порции из 20 записей.
Пробигаясь по ним циклом и создавая еще 20 обращений в БД, но быстрых, т.к. выборка по PRIMARY KEY.

Вопрос возник потому, что я не совсем четко представляю как работает UNION c сортировкой и ограничением LIMIT общих результатов.

Вот, например, взять только один SELECT из примера, допустим совпадения только по полнотекстовому поиску возвратит 1000 строк, как MySQL поступит, он все выбранные строки во временную таблицу скинет, соответственно выполнив все объединения из SELECTа или он будет ограничивать вставку строк во временную таблицу учитывая LIMIT в конце запроса?
Если он не обращает внимания на LIMIT, то значит 1000 объединений пройдет в пустую, т.к. будут отброшены потом, нужны лишь только 20 из них!

Что касается количества записей, таблицы растут, постоянно месяц назад они были в два раза меньше, что будет через пару месяцев вобще сложно предсказать, также растет посещаемость и соответственно больше людей хотят воспользоваться поиском.
А запрос хочется спроэктировать сразу правильно и оптимально, с учетом логики работы оптимизатора, не важно сколько там записей 100 или десять миллионов.

Конкретика вот
Количесво записей в четырех таблицах 1000 + 2000 + 300 + 300
Средняя посещаемость ресурса 5000 уников в сутки.
 

akd

dive now, work later
Команда форума
я так понял, вопросы только оттого, что лень прочитать ман?
как работает лимит и юнион там написано.
как мускул оптимизирует запросы там тоже написано.

Структуру не изменить, слить их в одну не получится или как-то переделать тоже не получится.
а сделать это все-таки придется, не сейчас, так в будущем. :)
 

denisimus77

Новичок
Автор оригинала: akd
я так понял, вопросы только оттого, что лень прочитать ман?
Да нет читал и в книге и в электронном виде, но не нашел конкретно того о чем спрашиваю
 

algo

To the stars!
Тебя это все абсолютно правильно смущает. Сделать выборку подобного рода на MySQL невозможно, для этого нет нужных средств.
 

denisimus77

Новичок
Автор оригинала: camka
По умолчанию при полнотекстовой выборке (если это не boolean mode) mysql уже сам сортирует результат по релевантности. Поэтому достаточно будет извлечь по 20 первых записей из каждой таблицы, не сортируя, и уже потом отсортировать эти 60 записей по релевантности и показать первые 20.

Однако, это только для первой выборки в 20 элементов. С постраничным выводом будет сложнее.
Да он сортирует результат, верно.
Я понял, что улучшить ситуацию можно только если в каждый из SELECTов добавить свой LIMIT 0, 20 (LIMIT 0, 40 и т.д.) в зависимости от того какая страница. Чем дальше от начала поиска тем тяжелее временная таблица будет и тем дольше будет выполняться запрос.
Таким образом имеет смысл в UNION включать только ту таблицу по которой происходит полнотекстовый поиск и выбирать поля только из этой таблицы, лишние не включать, их потом через цикл сибирать.
Можно еще потестить конечно два этих варианта.

-~{}~ 22.03.07 14:38:

А если LIMIT внутри поставить быстрее действительно работает.
Но тогда не работает SQL_CALC_FOUND_ROWS как нужно, общее число строк становится урезанным, т.к. внутри есть LIMIT. :-( Limit не получается использовать иначе придется такой запрос заново запускать без ограничений, прихожу к тому от чего уходил...
 

camka

не самка
denisimus77
Смотри, ты же сам делаешь сначала упорядочивание по вычисляемому значению для ВСЕХ найденных записей. Соответственно и объединения будут со ВСЕМИ записями. Оптимизатор не настолько умён, чтобы додумывать логику выполнения таких запросов. Таким образом, выбирай сначала только по полнотекстовому поиску без JOIN-ов, если возможно, сохраняй во временную таблицу, и дальше уже соединяй с остальными таблицами.
 
Сверху