Элементарный запрос... а столько проблем... - Дизайн и модификация Invision Power Board

Перейти к содержимому

 

Правила раздела

Здесь обсуждаются вопросы по настройке и администрированию форумов IPB 3.x.
Пожалуйста, не оффтопьте, если зашли сюда случайно, и обратите внимание на соседние разделы.
Установка, настройка и обслуживание форумов IPB 2.x.
Оформление форумов, включая верстку скинов.
Размещение рекламы на форумах.
SEO оптимизация форума.
Техническая поддержка наших скинов и модов.

СвернутьПрикрепленные теги

Теги не найдены

Страница 1 из 1

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

#1 Пользователь не на сайте   cyrax_02 ответил: »

 
 
  • Advanced
  • ***
  • Insert nick to fast reply form
  • Quote selected text to fast reply form
  • Группа: Пользователи
  • Сообщений: 135
  • Регистрация: 19-Февраль 15
  • Репутация: 0
  • IPB version:3.4.x
 

Отправлено 16 Июнь 2015 - 14:51

Задача заключается в выборке последних 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 и какие индексы для этого нужны.

Сообщение изменено: cyrax_02 (16 Июнь 2015 - 14:56)

0

#2 Пользователь не на сайте   siv1987 ответил: »

 
 
  • Advanced
  • Insert nick to fast reply form
  • Quote selected text to fast reply form
  • Группа: IPB Skins Team
  • Сообщений: 8 694
  • Регистрация: 20-Март 09
  • Репутация: 2 262
  • IPB version:3.1.x
 

Отправлено 16 Июнь 2015 - 19:41

Просмотреть сообщениеcyrax_02 сказал(а):

В таблицу (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, иначе это будет тупое левосторонние присоединение без необходимого отбора данных.

Просмотреть сообщениеcyrax_02 сказал(а):

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

Нужен сам EXPLAIN.

Просмотреть сообщениеcyrax_02 сказал(а):

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

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

#3 Пользователь не на сайте   cyrax_02 ответил: »

 
 
  • Advanced
  • ***
  • Insert nick to fast reply form
  • Quote selected text to fast reply form
  • Группа: Пользователи
  • Сообщений: 135
  • Регистрация: 19-Февраль 15
  • Репутация: 0
  • IPB version:3.4.x
 

Отправлено 16 Июнь 2015 - 22:39

Цитата

Вместо 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 не умеет с таблицами работать, а в текстовом виде каша получается.
0

#4 Пользователь не на сайте   siv1987 ответил: »

 
 
  • Advanced
  • Insert nick to fast reply form
  • Quote selected text to fast reply form
  • Группа: IPB Skins Team
  • Сообщений: 8 694
  • Регистрация: 20-Март 09
  • Репутация: 2 262
  • IPB version:3.1.x
 

Отправлено 16 Июнь 2015 - 22:50

Просмотреть сообщениеcyrax_02 сказал(а):

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

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

Цитата

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

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

#5 Пользователь не на сайте   cyrax_02 ответил: »

 
 
  • Advanced
  • ***
  • Insert nick to fast reply form
  • Quote selected text to fast reply form
  • Группа: Пользователи
  • Сообщений: 135
  • Регистрация: 19-Февраль 15
  • Репутация: 0
  • IPB version:3.4.x
 

Отправлено 16 Июнь 2015 - 23:27

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

Цитата

Запрос может медленно выполняться из-за
а) присоединения 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 раз быстрее всех прочих испробованных вариантов.
Конечно, такой вариант не идеален, поскольку подходящих топиков может быть очень много (выборка, реализуемая первым запросом будет очень большой и как следствие, медленной), но в качестве временного решения вполне сгодится.

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

Сообщение изменено: cyrax_02 (16 Июнь 2015 - 23:30)

0

#6 Пользователь не на сайте   siv1987 ответил: »

 
 
  • Advanced
  • Insert nick to fast reply form
  • Quote selected text to fast reply form
  • Группа: IPB Skins Team
  • Сообщений: 8 694
  • Регистрация: 20-Март 09
  • Репутация: 2 262
  • IPB version:3.1.x
 

Отправлено 16 Июнь 2015 - 23:57

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

Просмотреть сообщениеcyrax_02 сказал(а):

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

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

Просмотреть сообщениеcyrax_02 сказал(а):

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

INNER JOIN [SUBQUERY]

Просмотреть сообщениеcyrax_02 сказал(а):

При отсутствии сортировки фактически 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


0

#7 Пользователь не на сайте   cyrax_02 ответил: »

 
 
  • Advanced
  • ***
  • Insert nick to fast reply form
  • Quote selected text to fast reply form
  • Группа: Пользователи
  • Сообщений: 135
  • Регистрация: 19-Февраль 15
  • Репутация: 0
  • IPB version:3.4.x
 

Отправлено 17 Июнь 2015 - 12:59

Цитата

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

+----+-------------+---------------+--------+--------------------------------------------------------------------+----------------------------+---------+----------------------------+------+----------------------------------------------+
| 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                        |
+----+-------------+---------------+--------+--------------------------------------------------------------------+----------------------------+---------+----------------------------+------+----------------------------------------------+


Цитата

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

Оставшиеся условия отбора можно проверять, двигаясь с конца набора, пока не наберём 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 подходящих топиков. Только такая логика (без изменения структуры таблиц) даст приемлемую скорость выполнения запроса на большом числе топиков и тегов.
0

#8 Пользователь не на сайте   siv1987 ответил: »

 
 
  • Advanced
  • Insert nick to fast reply form
  • Quote selected text to fast reply form
  • Группа: IPB Skins Team
  • Сообщений: 8 694
  • Регистрация: 20-Март 09
  • Репутация: 2 262
  • IPB version:3.1.x
 

Отправлено 17 Июнь 2015 - 13:56

Просмотреть сообщениеcyrax_02 сказал(а):

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

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

Просмотреть сообщениеcyrax_02 сказал(а):

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

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

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

#9 Пользователь не на сайте   siv1987 ответил: »

 
 
  • Advanced
  • Insert nick to fast reply form
  • Quote selected text to fast reply form
  • Группа: IPB Skins Team
  • Сообщений: 8 694
  • Регистрация: 20-Март 09
  • Репутация: 2 262
  • IPB version:3.1.x
 

Отправлено 17 Июнь 2015 - 18:27

Просмотреть сообщениеcyrax_02 сказал(а):

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

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

#10 Пользователь не на сайте   cyrax_02 ответил: »

 
 
  • Advanced
  • ***
  • Insert nick to fast reply form
  • Quote selected text to fast reply form
  • Группа: Пользователи
  • Сообщений: 135
  • Регистрация: 19-Февраль 15
  • Репутация: 0
  • IPB version:3.4.x
 

Отправлено 17 Июнь 2015 - 18:52

Цитата

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

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

#11 Пользователь не на сайте   siv1987 ответил: »

 
 
  • Advanced
  • Insert nick to fast reply form
  • Quote selected text to fast reply form
  • Группа: IPB Skins Team
  • Сообщений: 8 694
  • Регистрация: 20-Март 09
  • Репутация: 2 262
  • IPB version:3.1.x
 

Отправлено 17 Июнь 2015 - 19:27

Просмотреть сообщениеcyrax_02 сказал(а):

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

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

Сообщить об этой теме:


Страница 1 из 1


Быстрый ответ

  

1 пользователей читают эту тему
0 зарегистрированных, 1 гостей, 0 скрытых


Контактная информация

Вопросы по работе сайта

+7 (917) 501-4765
C 10 до 20 в рабочие дни (время московское)

Техническая поддержка

Контактные данные специалистов

Дизайн форумов

IPB 3.x ¦ IPB 2.x

Бесплатные шаблоны

IPB 3.2 – 3.4 ¦ IPB 3.1 ¦ IPB 3.0 ¦ IPB 2.2 – 2.3 ¦ IPB 2.1 ¦ Клипарт
Лицензия на использование ¦ Ваша поддержка ¦ О проекте
Copyright © 2005-2016 IPBSkins.ru Team
При копировании материалов с сайта
прямая ссылка на источник обязательна