MySQL Медленно при соединении. Любой способ ускорить

Я больше разбираюсь в 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'

Это отфильтрует записи за 15 августа 2009 г. и позволит использовать любые индексы в этом столбце в качестве кандидатов. Обратите внимание, что 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-адреса, и в этом случае вам нужно правильное соединение, чтобы заставить его не включать строку без соответствующего значения.

Я бы добавил новые индексы, как упоминают другие. В частности, я бы добавил: 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
;

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

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

Вместо этого пусть listentrack удерживает music_id и присоединяйтесь к нему. Избавляется от текстового поиска, а также от дополнительного индекса.

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

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

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

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

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

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

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

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

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

СОЗДАЙТЕ ИНДЕКС music_url_index ON музыки (url), ИСПОЛЬЗУЯ BTREE; СОЗДАЙТЕ ИНДЕКС listenTrack_url_index ON listenTrack (url), ИСПОЛЬЗУЯ BTREE;

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