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 и заполнить ее, и добавить на нее индекс. Но это все слишком много работы. Если ли другие варианты как увеличить скорость такого запроса или все таки придется заниматься денормализацией?

 

[Фанат]

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

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

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

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

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

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

 

[halva]

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

 

[halva]

@Фанат, есть предложения?

 

[Фанат]

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

 

[Фанат]

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

 

[halva]

@Фанат,

 

[Фанат]

а. ну тогда повыкидывай весь этот ужас, оставь только на name

 

[halva]

а. ну тогда повыкидывай весь этот ужас, оставь только на name

он как бы не помогает, я пробовал

 

[Фанат]

вообще странно.
там все имена одинаковые что ли?

 

[halva]

вообще странно.
там все имена одинаковые что ли?

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

 

[halva]

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

 

[Фанат]

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

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

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

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

 

[halva]

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

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

Этот запрос генерируется автоматически системой, на списке записей, где есть возможность сортировки и пагинация.

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

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

 

[Фанат]

есть

 

[Фанат]

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

 

[halva]

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

Тут черным по белому написано, что на cases.deleted индекс не стоит.

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

Но впрочем, возможно, полезнее будет на casesqueue_name, надо смотреть по результатам

это показывает, что ты на момент написания этого сообщения даже не читал нормально запрос

Я, кстати, вчитался в условия джойнов - там какая-то белиберда

Тут ты уже начал читать запрос

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

Мусор типа cases.casesqueue_id != '' был добавлен специально, так как в некоторых сценариях выпадает индекс по этому полю из плана запроса

а. ну тогда повыкидывай весь этот ужас, оставь только на name

добавил, еще до того как ты посоветовал, запрос не стал работать быстрее

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

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

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

 

[WMix]

на языке инженеров

https://www.youtube.com/watch?v=bXN6fciKGMQ

 

[Фанат]

на языке инженеров

https://www.youtube.com/watch?v=rp8hvyjZWHs

 

[Фанат]

У меня нет проблемы с таблицей cases, а если и появятся я в силах прочитать EXPLAIN.

Эксплейн как бы говорит тебе, что проблемы только в таблице cases.

В любом случае, я думаю, что дальнейшая дискуссия смысла не имеет