Mysql Сортировка больших данных

Статус
В этой теме нельзя размещать новые ответы.

halva

Новичок
Есть что то типа такого запроса:
Код:
SELECT  cases.id , cases.casesqueue_id , cases.contact_id , cases.assigned_user_id , cases.case_number  , casesqueue_table.name casesqueue_name, cases.created_by
FROM cases
LEFT JOIN  casesqueues casesqueue_table ON cases.casesqueue_id != '' AND cases.casesqueue_id=casesqueue_table.id AND casesqueue_table.deleted=0 AND casesqueue_table.deleted=0 
LEFT JOIN  contacts contacts_table ON cases.contact_id != '' AND cases.contact_id=contacts_table.id AND contacts_table.deleted=0  AND contacts_table.deleted=0 
LEFT JOIN  users jt2 ON cases.assigned_user_id != '' AND cases.assigned_user_id=jt2.id AND jt2.deleted=0 AND jt2.deleted=0
WHERE cases.deleted=0 ORDER BY casesqueue_name ASC LIMIT 20
На сервере около 80т записей, такой запрос отрабатывает 2-2.5 секунды. При том если отфильтровать количество сортируемых записей до количества меньше 1000, то запрос будет обрабатывать в сотни раз быстрее. Я так понимаю он тупо тратит много времени на выборку этих данных (Sending data 0.179417, на тестовых данных ), когда нет фильтров.

Мне надо это как то оптимизировать и единственный вариант, который я могу придумать на данный момент это денормализация таблицы cases, то есть создать колонку casesqueue_name и заполнить ее, и добавить на нее индекс. Но это все слишком много работы. Если ли другие варианты как увеличить скорость такого запроса или все таки придется заниматься денормализацией?
 
Последнее редактирование модератором:

Фанат

oncle terrible
Команда форума
Чтобы было сразу понятно, это очень смешной вопрос.
Большие данные - это в лучшем случае 80 миллионов записей, а не тысяч. А по-хорошему в big data счет идет на миллиарды и триллионы.

И ни о какой денормализации тут тоже не идет речь. Здесь мизерное количество данных и абсолютно примитивный запрос.

Все что надо сделать - это проставить ИНДЕКСЫ. причем индексы - это первое, о чем должен думать программист.
То есть вопрос звучит примерно так: "У меня вопрос по экономической политике: надо яичницу пожарить, а сковродка какая-то неудобная. пока в голову приходит основать металлургический завод."

Чтобы было понятно - я не издеваюсь. Просто показываю, как вопрос выглядит со стороны.

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

Поскольку у тебя джойн по нескольким полям, то может понадобиться составной индекс
 

halva

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

Снимок экрана 2019-01-25 в 14.45.13.png
 

Фанат

oncle terrible
Команда форума
Да, разумеется. Тут черным по белому написано, что на cases.deleted индекс не стоит.
Но впрочем, возможно, полезнее будет на casesqueue_name, надо смотреть по результатам
 

Фанат

oncle terrible
Команда форума
Я, кстати, вчитался в условия джойнов - там какая-то белиберда
по сути везде нужно только одно условие, которое в итоге и срабатывает, а остальное - просто мусор.
 

Фанат

oncle terrible
Команда форума
а. ну тогда повыкидывай весь этот ужас, оставь только на name
 

Фанат

oncle terrible
Команда форума
вообще странно.
там все имена одинаковые что ли?
 

halva

Новичок
вообще странно.
там все имена одинаковые что ли?
Много одинаковых (в casesqueues 73 записи), но явно видно по EXPLAIN что он даже не рассматривает возможность использования индекса casesqueues.name при сортировке, то есть добавляй не добавляй индекс на name в casesqueues, EXPLAIN не меняется.
 

halva

Новичок
@Фанат, смотря на результаты профайлера я вижу, что запрос который пытается сортировать все записи в таблице по связанному через JOIN столцу, очень много времени тратит на отправку данных и сохранение HEAP на диск, что собственно логично. Тот же самый запрос, но с фильтрацией по cases.casesqueue_id = 1223 не делает этого? так как записей в разы меньше. Что сделать, что бы ему не надо было отправлять эти данные? Какие индексы навешать?
Снимок экрана 2019-01-25 в 15.28.51.png Снимок экрана 2019-01-25 в 15.31.17.png
 

Фанат

oncle terrible
Команда форума
да профайлер это ерунда, он вообще всё в sending data пишет
в данном случае проблема именно в одинаковых именах

и какая связь между уникальностью имён в cases и оличеством записей в casesqueues?
Судя по всему, в структуре таблиц такой же бордель с канканом, что и в запросе.

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

Зачем у тебя там в конце сортировка? ты будешь пагинацию делать?
 

halva

Новичок
@Фанат, сортировка по индексируемым полям, которые есть в таблице cases не составляет проблем, проблема именно при сортировки по колонкам которые соединяются с cases через left join, то есть в данном случае casesqueues.

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

Для начала убери джойны, они тут ни на что не влияют, и экспериментируй чисто с cases.
Какой смысл в таком запросе? он вообще не относится к этому сценарию, да и там нет проблем с производительностью.
 

Фанат

oncle terrible
Команда форума
не нужно провоцировать админа своим тявканьем. он же может повестись, и просто выпинать тебя с форума.
все что тебе нужно знать о твоей проблеме - я написал.
если ты чего-то не понимаешь - это твоя проблема, не надо обвинять в ней кого-то другого.
 

halva

Новичок
@Фанат, хорошо, давай я тебе поясню на языке инженеров что, то что ты на данные момент насоветовал ни разу не решило проблему

Тут черным по белому написано, что на cases.deleted индекс не стоит.
Даже если его добавлять, разницы нет, так как большинство записей в таблице это deleted = 0

Но впрочем, возможно, полезнее будет на casesqueue_name, надо смотреть по результатам
это показывает, что ты на момент написания этого сообщения даже не читал нормально запрос
Я, кстати, вчитался в условия джойнов - там какая-то белиберда
Тут ты уже начал читать запрос
по сути везде нужно только одно условие, которое в итоге и срабатывает, а остальное - просто мусор.
Мусор типа cases.casesqueue_id != '' был добавлен специально, так как в некоторых сценариях выпадает индекс по этому полю из плана запроса
а. ну тогда повыкидывай весь этот ужас, оставь только на name
добавил, еще до того как ты посоветовал, запрос не стал работать быстрее
Для начала убери джойны, они тут ни на что не влияют, и экспериментируй чисто с cases. поскольку время выполнения будет такое же, а запрос не будет вызывать нервный тик у тех, кто его видит.
Тут ты мне предлагаешь, убрать джойны, а смысл всего топика, что проблема именно при этом сценарии.
Любая сортировка по полям в cases выполняется за 0.001-0.003 секунды. У меня нет проблемы с таблицей cases, а если и появятся я в силах прочитать EXPLAIN.
Да можно повешать индекс на deleted, но я его добавлял и он не дает ни какого прироста к времени выполнения.

Исходя из всего что ты мне написал, что такого я узнал нового о своей проблеме? От тебя я лишь узнал, что я "говно", что заброс "говно", что там нет индекса на deleted, и что профилировщик "говно" (бесполезная тулза). А тема о другом. После первого твоего комментария, я ожидал от тебя, что ты скажешь мне где реальное "говно" и пояснишь за "жизнь" (где это говно и почему это говно случилось), а я тебя с удовольствием поблагодарю. Но ты втюхиваешь мне какую-то дичь и угрожаешь админской "писькой".
 

Фанат

oncle terrible
Команда форума
У меня нет проблемы с таблицей cases, а если и появятся я в силах прочитать EXPLAIN.
Эксплейн как бы говорит тебе, что проблемы только в таблице cases.

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