Производительность MYSQL медленная с использованием сортировки файлов

Проблема в том, что MySQL использует только один индекс при выполнении запроса. Если вы добавите новый индекс, который использует 3 поля в предложении WHERE, строки будут найдены быстрее.

ALTER TABLE `adverts` ADD INDEX price_status_approved(`price`, `status`, `approved`);

Согласно документации MySQL ORDER BY Optimization:

В некоторых случаях MySQL не может использовать индексы для разрешения ORDER BY, хотя он по-прежнему использует индексы для поиска строк, соответствующих предложению WHERE. К таким случаям относятся следующие:
Ключ, используемый для выборки строк, не совпадает с ключом, используемым в ORDER BY.

Вот что происходит в вашем случае. Как сообщает нам вывод EXPLAIN, оптимизатор использует ключ price для поиска строк. Однако ORDER BY находится в поле date_updated, которое не принадлежит ключу price.

Чтобы быстрее находить строки И сортировать строки быстрее, вам нужно добавить индекс, который содержит все поля, используемые в предложениях WHERE и ORDER BY:

ALTER TABLE `adverts` ADD INDEX status_approved_date_updated(`status`, `approved`, `date_updated`);

Поле, используемое для сортировки, должно находиться на последней позиции в индексе. Бесполезно включать price в индекс, потому что условие, используемое в запросе, вернет диапазон значений.

Если EXPLAIN по-прежнему показывает, что он использует файловую сортировку, вы можете попробовать заставить MySQL использовать выбранный вами индекс:

SELECT adverts.*
FROM adverts
FORCE INDEX(status_approved_date_updated)
WHERE price >= 0
AND adverts.status = 1
AND adverts.approved = 1
ORDER BY date_updated DESC 
LIMIT 19990, 10

Обычно нет необходимости форсировать индекс, потому что оптимизатор MySQL чаще всего делает правильный выбор. Но иногда он делает плохой выбор или не самый лучший выбор. Вам нужно будет запустить несколько тестов, чтобы увидеть, улучшает ли это производительность или нет.

Снимите галочки вокруг '0' - в настоящее время это может помешать использованию индекса, но я не уверен. Тем не менее, это лучший стиль, поскольку цена имеет тип int, а не символьный столбец.

SELECT adverts .*    
FROM adverts
WHERE (
price >= 0
)
AND (
adverts.status = 1
)
AND (
adverts.approved = 1
)
ORDER BY date_updated DESC 
LIMIT 19990 , 10

MySQL не использует ключ date_updated для сортировки, а просто использует ключ price, поскольку он используется в предложении WHERE. Вы можете попробовать использовать подсказки индекса:

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Добавьте что-то вроде

USE KEY FOR ORDER BY  (date_updated)

У меня есть два предложения. Во-первых, удалите кавычки вокруг нуля в предложении where. Эта строка должна быть:

price >= 0

Во-вторых, создайте этот индекс:

CREATE INDEX `helper` ON `adverts`(`status`,`approved`,`price`,`date_created`);

Это должно позволить MySQL найти 10 строк, указанных в вашем предложении LIMIT, используя только индекс. Сортировка файлов сама по себе неплоха... количество строк, которые необходимо обработать.

Ваше условие WHERE использует price, status, approved для выбора, а затем date_updated используется для сортировки.

Поэтому вам нужен единый индекс с этими полями; Я предлагаю индексировать approved, status, price и date_updated именно в таком порядке.

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

CREATE INDEX advert_ndx ON adverts (approved, status, price, date_updated);

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

Я бы также удалил все ненужные индексы, что ускорило бы INSERTs и UPDATEs.