Оптимизация производительности MySQL: порядок полем даты и времени
3 ответа
Создайте сводный индекс любой на postings (is_active, post_date) (в том порядке).
Это будет использоваться оба для фильтрации на is_active и упорядочивание post_date.
MySQL должен показать REF метод доступа по этому индексу в EXPLAIN EXTENDED.
Обратите внимание, что у Вас есть a RANGE фильтрация условия user_offtopic_count, вот почему Вы не можете использовать индекс по этому полю и в фильтрации и в сортировке по другому полю.
В зависимости от того, насколько выборочный Ваш user_offtopic_count (т.е. сколько строк удовлетворяет user_offtopic_count < 10), может быть более полезно создать индекс на user_offtopic_count и позвольте post_dates быть отсортированным.
Чтобы сделать это, создайте сводный индекс на postings (is_active, user_offtopic_count) и удостоверьтесь RANGE метод доступа по этому индексу используется.
То, какой индекс будет быстрее, зависит от Ваших данных distribuion. Создайте оба индекса, FORCE их и видят, который быстрее:
CREATE INDEX ix_active_offtopic ON postings (is_active, user_offtopic_count);
CREATE INDEX ix_active_date ON postings (is_active, post_date);
SELECT
`postings`.`id`,
UNIX_TIMESTAMP(postings.post_date) as post_date,
`postings`.`link`,
`postings`.`title`,
`postings`.`author`,
`postings`.`excerpt`,
`postings`.`long_excerpt`,
`feeds`.`title` AS feed_title,
`feeds`.`website` AS feed_website
FROM
`postings` FORCE INDEX (ix_active_offtopic)
JOIN
`feeds`
ON
`feeds`.`id` = `postings`.`feed_id`
WHERE
`feeds`.`type` = 1 AND
`postings`.`user_offtopic_count` < 10 AND
`postings`.`is_active` = 1
ORDER BY
`postings`.`post_date` desc
LIMIT
15
/* This should show RANGE access with few rows and keep the FILESORT */
SELECT
`postings`.`id`,
UNIX_TIMESTAMP(postings.post_date) as post_date,
`postings`.`link`,
`postings`.`title`,
`postings`.`author`,
`postings`.`excerpt`,
`postings`.`long_excerpt`,
`feeds`.`title` AS feed_title,
`feeds`.`website` AS feed_website
FROM
`postings` FORCE INDEX (ix_active_date)
JOIN
`feeds`
ON
`feeds`.`id` = `postings`.`feed_id`
WHERE
`feeds`.`type` = 1 AND
`postings`.`user_offtopic_count` < 10 AND
`postings`.`is_active` = 1
ORDER BY
`postings`.`post_date` desc
LIMIT
15
/* This should show REF access with lots of rows and no FILESORT */
MySQL имеет два filesort алгоритма: более старый filesort, который виды записывают на диске и новой версии, которая работает в памяти.
Если это не может использовать индекс на первой таблице в соединении для сортировки запроса, это должно будет сделать filesort. Если набор результатов прежде, чем отсортировать преобразованный в формат фиксированной ширины больше, чем буфер вида ИЛИ если он содержит какие-либо текстовые поля, он должен будет использовать более медленный дисковый filesort алгоритм (второе условие удовлетворено, так как Ваш запрос имеет текстовое поле).
MySQL принимает решение использовать is_active столбец, якобы потому что он думает, что столбец является самым выборочным в устранении строк, прежде чем он продолжит другие соединения и где условия. Первая вещь, которую я предложил бы, будет состоять в том, чтобы попытаться создать сводные индексы с post_date, feed_id, и столбцы в где условие, например, (is_active, user_offtopic_count, post_date, feed_id).
Также важно помнить, что MySQL не будет использовать индекс, если к столбцу, по которому вы сортируете, применяется функция.
Вам также следует попробовать использовать псевдонимы postings.post_date как что-то другое. Это скажет MySQL упорядочить по неизмененному столбцу, и вы все равно выберете временную метку unix.