Элементарный запрос... а столько проблем...

[cyrax_02 1]

Задача заключается в выборке последних 7 топиков с содержимым первого поста, которые (топики) имеют один из указанных тегов

 

В таблицу (ipb_core_tags) добавил индекс по полю (tag_text)

В таблицу (ipb_topics) добавил следующие индексы (не все из них нужны, но пусть MySQL сам выбирает):

(state)

(approved, state)

(approved, state, start_date)

 

Индекс по полю (start_date) имеется изначально

 

Вот такой запрос выполняется долго:

SELECT DISTINCT `ipb_topics`.last_post AS date, `ipb_topics`.title AS title, `ipb_posts`.post AS content
FROM `ipb_topics`
LEFT JOIN `ipb_posts` ON (`ipb_topics`.topic_firstpost = `ipb_posts`.pid)
LEFT JOIN `ipb_core_tags` ON ((`ipb_topics`.tid = `ipb_core_tags`.tag_meta_id) AND (`ipb_core_tags`.tag_text IN ('Тег1', 'Тег2')) AND (`ipb_core_tags`.tag_meta_app = 'forums') AND (`ipb_core_tags`.tag_meta_area = 'topics'))
WHERE ((`ipb_topics`.approved = 1) AND (`ipb_topics`.state != 'link'))
ORDER BY `ipb_topics`.start_date DESC LIMIT 7

Согласно EXPLAIN'у:

- для выборки топиков используется индекс (approved, state, start_date)

- для выборки постов - первичный ключ таблицы постов

- для выборки тегов - индекс (tag_text)

 

Наблюдения:

Если убрать сортировку либо убрать оба JOIN'а (а сортировку оставить) - запрос выполняется мгновенно.

Если убрать JOIN для тегов - запрос в разы ускоряется, но от мгновенного выполнения по-прежнему далёк

 

Запрос может медленно выполняться из-за

а) присоединения 2 таблиц

б) или из-за медленной сортировки (сортировка всех топиков налету), наблюдаемой только при наличии присоединённых таблиц ?

Но поскольку при удалении сортировки (join'ы остаются) запрос начинает выполняться мгновенно, то причина - в б)

 

Вопрос:

Почему при наличии присоединённых таблиц сортировка выполняется медленно ?

Вероятно, индекс (approved, state, start_date), используемый MySQL, для сортировки таки не используется и выполняется полная сортировка всех топиков налету, после чего применяется лимит.

 

Если так, то почему MySQL не использует индекс (approved, state, start_date) для сортировки ? Ведь этот индекс уже содержит готовый отсортированный список топиков, удовлетворяющий всем WHERE-условиям. Т.е. нужно всего-то прочитать 7 последних строк этого индекса для требуемого ключа...

 

MySQL до этого не может догадаться или я чего-то не допонимаю ?

 

-----------------------------------------------------------------------------------------------------------------------------------------------

P.S. В связи с тем, что предстоит переход на IPB4, сабжевое решение является временным и не использует движок IPB

Соответственно, вопрос заключается не в том, как с помощью IPB получить сабжевые данные, а в том, как построить сабжевый запрос на чистом SQL и какие индексы для этого нужны.

Edited June 16, 2015 by cyrax_02

[siv1987 2,628]

В таблицу (ipb_topics) добавил следующие индексы (не все из них нужны, но пусть MySQL сам выбирает):

(state)

(approved, state)

(approved, state, start_date)

Последние два индекса можно считать равнозначными в зависимости от того, какие поля используются - один из них лишний. state не нужен.

Вместо tag_text оптимизатор скорее всего будет использовать индекс tag_app который подходит для двух условий ct.tag_meta_app = 'forums' AND ct.tag_meta_area = 'topics' (ref const). Если предположить, что большинство тегов в таблице относятся к темам, то использование этого индекса ничуть не ускоряет выборку. В данном случае предпочтительнее будет FORCE INDEX (tag_text) так как он значительно уменьшает количество отбираемых данных. Как вариант можно попробовать создать индекс по всем полям - tag_text, tag_meta_id, tag_meta_app, tag_meta_area. Правда не знаю как поведет себя такой индекс при использование оператора IN и есть ли в нем смысл.

Также есть смысл LEFT JOIN заменить на INNER JOIN для ibf_core_tags, иначе это будет тупое левосторонние присоединение без необходимого отбора данных.

 

Согласно EXPLAIN'у:

Нужен сам EXPLAIN.

 

Если так, то почему MySQL не использует индекс (approved, state, start_date) для сортировки ? Ведь этот индекс уже содержит готовый отсортированный список топиков, удовлетворяющий всем WHERE-условиям.

Лучше всего спросить об этом на специализированных форумах по SQL.

[cyrax_02 1]

Вместо tag_text оптимизатор скорее всего будет использовать индекс tag_app который подходит для двух условий ct.tag_meta_app = 'forums' AND ct.tag_meta_area = 'topics' (ref const)...

В данном случае предпочтительнее будет FORCE INDEX (tag_text) так как он значительно уменьшает количество отбираемых данных.

При присоединении таблицы тегов как раз таки используется индекс (tag_text).

А при выполнении внешнего условия WHERE - используется индекс (approved, state, start_date), как я уже написал выше.

Это если речь о том запросе, который я привёл в первом посте.

 

Также есть смысл LEFT JOIN заменить на INNER JOIN для ibf_core_tags, иначе это будет тупое левосторонние присоединение без необходимого отбора данных.

Да, верно. Но на скорость выполнения это не влияет.

LEFT JOIN здесь нужен в том случае, когда проверяется не на IN, а на NOT IN. В последнем случае в условие присоединения таблицы тегов нужно к NOT IN через OR добавить условие IS NULL, либо этот IS NULL добавить во внешний WHERE)

 

Нужен сам EXPLAIN

CKEditor не умеет с таблицами работать, а в текстовом виде каша получается.

[siv1987 2,628]

При присоединении таблицы тегов как раз таки используется индекс (tag_text).

Покажите explain запроса.

 

CKEditor не умеет с таблицами работать, а в текстовом виде каша получается.

Используйте 'raw' результат или оформите таблицу в теге code.

[cyrax_02 1]

В общем-то картина прояснилась:

 

Запрос может медленно выполняться из-за

а) присоединения 2 таблиц

б) или из-за медленной сортировки (сортировка всех топиков налету), наблюдаемой только при наличии присоединённых таблиц ?

Но поскольку при удалении сортировки (join'ы остаются) запрос начинает выполняться мгновенно, то причина - в б)

Как оказалось, имеет место 3-й вариант:

в) из-за медленного (полного) присоединения таблицы тегов при наличии сортировки:

 

А объясняется это просто:

При отсутствии сортировки фактически join'ятся только первые 7 строк из таблица тегов (т.к. стоит LIMIT 7) - запрос выполняется быстро

При наличии сортировки join'ятся все строки из таблицы тегов, поскольку после сортировки в конечную выборку могут попасть любые из них.

 

Если так, то почему MySQL не использует индекс (approved, state, start_date) для сортировки ? Ведь этот индекс уже содержит готовый отсортированный список топиков, удовлетворяющий всем WHERE-условиям. Т.е. нужно всего-то прочитать 7 последних строк этого индекса для требуемого ключа...

А здесь ответ будет таким:

Для сортировки по датам индекс (approved, state, start_date) используется. А запрос медленно выполняется из-за join'а всех строк таблицы тегов.

 

------------------------------------------------------------------------------------------------------------------------------------------------

Соответственно, задача заключается в том, чтобы таблицы join'ились и внешнее условие WHERE проверялось по ходу следования записей (в соответствии с порядком ORDER BY) в индексе, содержащем дату в первой позиции. Т.е. нужен индекс, состоящий из даты или начинающися с даты. Но проблема в том, что MySQL не умеет работать по такой логике:

 

а) join'ить таблицы по ходу следования записей в некотором индексе (при наличии сортировки и лимита). Это подтверждает сабжевый запрос.

При наличии сортировки MySQL join'ит все присоединяемые записи, не смотря на то, что достаточно присоединить только первые 7 в том порядке, в котором расположены топики в этом индексе.

 

б) выполнять внешнее условие (часть внешнего условия) по ходу следования записей в некотором индексе (при наличии сортировки и лимита). Это подтверждает модификация сабжевого запроса, в котором условия

(`ipb_core_tags`.tag_text IN ('Тег1', 'Тег2')) AND (`ipb_core_tags`.tag_meta_app = 'forums') AND (`ipb_core_tags`.tag_meta_area = 'topics')

вынесены во общий WHERE

 

В этом случае MySQL для реализации условия WHERE применяет один из индексов, а затем полностью проходит по полученному набору данных (не смотря на то, что он отсортирован и что достаточно отобрать первые 7 с конца) и выполняет проверку остальных условий, после чего выполняет сортировку и LIMIT. А в идеале следовало бы воспользоваться отсортированным формой этого набора (полученного после применения индекса) и выполнять проверку остальных условий с конца до тех пор, пока не будут отобраны 7 топиков.

 

------------------------------------------------------------------------------------------------------------------------------------------------

В качестве временного решения вместо одного запроса сделал 2 простых запроса:

 

1) Из таблицы тегов получаем нужные идентификаторы топиков

2) Делаем выборку топиков из таблицы топиков по условию IN (ids)

 

Почему первый запрос не оформляю как вложенный подзапрос - потому что MySQL по неизвестным мне причинам выполняет внутренний запрос для каждой записи внешнего запроса, не смотря на то, что внутренний запрос никак не связан с внешним.

Такой вариант работает 0.1 сек - это в 15-20 раз быстрее всех прочих испробованных вариантов.

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

 

Ну а идеальный вариант - это совмещение выборки тегов с сортировкой и лимитом. Такой вариант, похоже, нереализуем с текущей структурой таблиц.

Edited June 16, 2015 by cyrax_02

[siv1987 2,628]

Мы так и не увидели EXPLAIN запроса. Повторяем, покажите нормальный EXPLAIN. И план запроса бы небыл лишним (SHOW PROFILES). Покажите запросы какие используете, мало ли что вы там составляете под "из таблицы тегов получаем нужные идентификаторы топиков".

Желательно также услышать про какие объемы данных идет речь. Был бы EXPLAIN хотябы можно было оценить с каким количеством строк mysql оперирует.

 

не смотря на то, что он отсортирован и что достаточно отобрать первые 7 с конца

Недостаточно потому что там есть условия отбора. Достаточно когда просто отобрать семь последних топиков.

 

Почему первый запрос не оформляю как вложенный подзапрос - потому что MySQL по неизвестным мне причинам выполняет внутренний запрос для каждой записи внешнего запроса, не смотря на то, что внутренний запрос никак не связан с внешним.

INNER JOIN [sUBQUERY]

 

При отсутствии сортировки фактически join'ятся только первые 7 строк из таблица тегов (т.к. стоит LIMIT 7) - запрос выполняется быстро

При наличии сортировки join'ятся все строки из таблицы тегов, поскольку после сортировки в конечную выборку могут попасть любые из них.

При наличие условия отбора тоже джоинятся все строки. Рассматривать сабжевый запрос следует с INNER JOIN (без которого теряет логику), либо с выведением части условия из джоина в условия отбора строк (WHERE).

 

SELECT t.last_post, t.title, p.post FROM ibf_topics t 
INNER JOIN (SELECT tag_meta_id FROM ibf_core_tags WHERE tag_text IN ('test2', 'test3') AND tag_meta_app='forums' AND tag_meta_area='topics') z ON z.tag_meta_id=t.tid 
LEFT JOIN ibf_posts p ON p.pid=t.topic_firstpost 
WHERE t.state!='link' AND t.approved=1
ORDER BY t.start_date DESC LIMIT 7

[cyrax_02 1]

Мы так и не увидели EXPLAIN запроса. Повторяем, покажите нормальный EXPLAIN.

[table]

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE ibf_topics range approved, tempState, tempApprovedState, tempApprovedStateStartdate tempApprovedStateSrartdate 28 NULL 2356 Using where; Using temporary; Using filesort

1 SIMPLE ibf_posts eq_ref PRIMARY PRIMARY 4 ibf_topics.topic_firstpost 1

1 SIMPLE ibf_core_tags range tag_app, tempTagText, tempTagTextAppArea tempTagText 768 NULL 38 Using where; Distinct

[/table]

 

Недостаточно потому что там есть условия отбора. Достаточно когда просто отобрать семь последних топиков.

Оставшиеся условия отбора можно проверять, двигаясь с конца набора, пока не наберём 7 топиков:

А в идеале следовало бы воспользоваться отсортированным формой этого набора (полученного после применения индекса) и выполнять проверку остальных условий с конца до тех пор, пока не будут отобраны 7 топиков.

 

INNER JOIN [sUBQUERY]

Но это не ответ на вопрос, а всего лишь обход проблемного вопроса.

 

При наличие условия отбора тоже джоинятся все строки.

При наличии условия отбора, но при отсутствии сортировки, присоединяются только первые 7 строк из таблицы тегов, которые удовлетворяют этому условию - запрос выполняется быстро. При наличии сортировки присоединяются все строки из таблицы тегов - запрос выполняется долго.

 

...либо с выведением части условия из джоина в условия отбора строк (WHERE).

SELECT t.last_post, t.title, p.post FROM ibf_topics t 
INNER JOIN (SELECT tag_meta_id FROM ibf_core_tags WHERE tag_text IN ('test2', 'test3') AND tag_meta_app='forums' AND tag_meta_area='topics') z ON z.tag_meta_id=t.tid 
LEFT JOIN ibf_posts p ON p.pid=t.topic_firstpost 
WHERE t.state!='link' AND t.approved=1
ORDER BY t.start_date DESC LIMIT 7

Раз уж мы отдельным запросом получаем нужные топики (из таблицы тегов), то нет смысла вытаскивать условия на link и approved во внешний запрос. Впрочем, это не важно. В любом случае, в этом запросе будет выполняться присоединение всех строк, полученных во вложенном INNER JOIN-запросе. И только потом будет выполнена сортировка всего полученного набора топиков и лимитирование.

 

А в идеале join'ы и лимитирование должны выполняться по отсортированному списку топиков с конца до тех пор, пока не будут отобраны 7 подходящих топиков. Только такая логика (без изменения структуры таблиц) даст приемлемую скорость выполнения запроса на большом числе топиков и тегов.

[siv1987 2,628]

В любом случае, в этом запросе будет выполняться присоединение всех строк, полученных во вложенном INNER JOIN-запросе.

Всех которые соответствуют условию, или там миллион тегов ему соответствует?.. Кроме того, не столь важен факт количества (в разумных объемов), так как джоин производится по первичному ключу и происходит быстро - аналогично вашему IN.

 

Раз уж мы отдельным запросом получаем нужные топики (из таблицы тегов), то нет смысла вытаскивать условия на link и approved во внешний запрос.

Подзапросом (subqeury) получаем только ид тем которые соответствует этим тегам, далее следует отобрать нужные темы.

Есть условия присоединения двух таблиц (JOIN) и есть условие отбора строк (WHERE). "Внешний/внутренний запрос" это что-то другое, я не знаю что, хотя и предполагаю что вы под этим подразумеваете условия отбора. В данном случае условие можно указать как в джоине так и в отборе строк, раз по этим полям есть составной индекс. Чисто теоретически мне привычнее видеть джоин одним условием по первичному ключу, а остальное в отборе. Но если говорить о смысле, то нужно видеть план запроса оба варианта.

 

Покажите профайлинг последнего sql запроса (show profile).

[siv1987 2,628]

CKEditor не умеет с таблицами работать, а в текстовом виде каша получается.

Копируете в обычном текстовом виде и вставляете в ббкод table. Парсер отформатирует его в читабельную таблицу.

[cyrax_02 1]

Копируете в обычном текстовом виде и вставляете в ббкод table. Парсер отформатирует его в читабельную таблицу.

Это Вы сами состряпали BB-код или он был в комплекте IPBoard ?

[siv1987 2,628]

Это Вы сами состряпали BB-код или он был в комплекте IPBoard ?

Этот сами состряпали в стиле а-ля mysql, но есть и сторонние ббкоды html таблиц.