MySQL Slow on join. Любой способ ускорить

Я скорее парень SQL Server, но эти понятия должны применяться.

Я бы добавил индексы:

1. В ListenTrack добавьте индекс с url и date_created
2. В разделе «Музыка» добавьте индекс с URL-адресом

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

По большей части вы также должны индексировать любой столбец, который используется в JOIN. В вашем случае вы должны индексировать как listentrack.url и music.url

@jeff s – Индекс music.date_created не поможет, потому что вы запускаете это с помощью функции, поэтому MySQL не может использовать индекс в этом столбце. Часто вы можете переписать запрос так, чтобы столбец с индексированными ссылками использовался статически, как:

 DATEDIFF(DATE(date_created),'2009-08-15') = 0 

становится

 date_created >= '2009-08-15' and date_created < '2009-08-15' 

Это позволит отфильтровать записи, относящиеся к 2009-08-15 годам, и дать возможность любым индексам в этом столбце быть кандидатами. Обратите внимание: MySQL не может использовать этот индекс, это зависит от других факторов.

Лучше всего сделать двойной указатель на listentrack(url, date_created) а затем еще один индекс на music.url

Эти 2 индекса будут охватывать этот конкретный запрос.

Обратите внимание: если вы запустите EXPLAIN в этом запросе, вы все равно получите using filesort потому что он должен записать записи во временную таблицу на диске, чтобы выполнить ORDER BY.

В общем, вы всегда должны запускать свой запрос в EXPLAIN чтобы получить представление о том, как MySQL выполнит запрос, а затем оттуда. См. Документацию EXPLAIN :

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

Попробуйте создать индекс, который поможет с соединением:

 CREATE INDEX idx_url ON music (url); 

Кажется, я мог пропустить это раньше. Почему вы вообще присоединяетесь к музыкальному столу? Кажется, вы вообще не используете данные в этой таблице, и вы выполняете левое соединение, которое не требуется, правильно? Я думаю, что эта таблица в запросе сделает ее намного медленнее и не добавит никакого значения. Возьмите все ссылки на музыку, если не требуется включение url, и в этом случае вам нужно право присоединиться, чтобы заставить его не включать строку без соответствующего значения.

Я бы добавил новые индексы, как говорят другие. В частности, я бы добавил: music url listentrack date_created, url

Это улучшит ваше соединение тонны.

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

Не уверен в синтаксисе от верхней части головы: где «2009-08-15 00:00:00» <= date_created <2009-08-16 00:00:00

Это должно позволить ему быстро использовать индекс для поиска соответствующих записей. Объединенный два ключевых индекса в музыке должны позволять ему находить записи на основе даты и URL-адреса. Вы должны поэкспериментировать, им может быть лучше идти в другом направлении url, date_created по индексу.

План объяснения для этого запроса должен сказать «использовать индекс» в правой колонке для обоих. Это означает, что ему не нужно ударять данные в таблице, чтобы рассчитать ваши суммы.

Я также проверил бы настройки памяти, которые вы настроили для MySQL. Похоже, что вам не хватает выделенной памяти. Будьте очень осторожны в отношении различий между настройками на сервере и настройками на основе потоков. Сервер с кешем 10 МБ довольно мал, поток с кешем 10 МБ может быстро использовать много памяти.

Иаков

Предварительная сгруппировка и последующее присоединение делают вещи намного быстрее с MySQL / MyISAM. (Я подозрительно, что это не требуется для других БД)

Это должно выполняться так же быстро, как и не присоединенная версия:

 SELECT total, a.url, title FROM ( SELECT COUNT(*) as total, url from listenTrack WHERE DATEDIFF(DATE(date_created),'2009-08-15') = 0 GROUP BY url ORDER BY total DESC LIMIT 0,10 ) as a LEFT JOIN music ON music.url = a.url ; 

PS – Сопоставление между двумя таблицами с идентификатором вместо URL-адреса является разумным советом.

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

У вас есть listentrack вместо music_id, и присоединяйтесь к этому. Получает освобождение от текстового поиска, а также дополнительный индекс.

Кроме того, это, вероятно, более правильно. Вы отслеживаете время прослушивания определенного трека, а не URL. Что, если URL-адрес изменится?

После добавления индексов вам может понадобиться изучить добавление нового столбца для date_created как unix_timestamp, что сделает математические операции быстрее.

Я не уверен, почему у вас есть функция diff, но, как оказалось, вы ищете все строки, которые были обновлены в определенную дату.

Вы можете посмотреть на свой запрос, поскольку он, кажется, имеет ошибку.

Если вы используете модульные тесты, вы можете сравнить результаты своего запроса и запроса, используя временную метку unix.

вы можете добавить индекс в поле url для обеих таблиц.

сказав, что, когда я преобразовал из mysql в SQL Server 2008, с теми же запросами и теми же структурами базы данных, запросы выполнялись на 1-3 порядка быстрее.

Я думаю, что некоторые из них связаны с rdbms (оптимизаторы mysql не так хороши …), и некоторые из них, возможно, связаны с тем, как ресурсы rdbms резервируют систему. хотя, сравнения были сделаны в производственных системах, где будет выполняться только db.

Это ниже, вероятно, будет работать для ускорения запроса.

CREATE INDEX music_url_index ON music (url) ИСПОЛЬЗОВАНИЕ BTREE; CREATE INDEX listenTrack_url_index ON listenTrack (url) ИСПОЛЬЗОВАНИЕ BTREE;

Вам действительно нужно знать общее количество сравнений и сканирование строк, которые происходят. Чтобы получить этот ответ, посмотрите на код здесь, как это сделать, используя объяснение http://www.siteconsortium.com/h/p1.php?id=mysql002 .