Как ускорить запрос с двумя датами?

Question

[pcdesign]

Вот такая таблица:



И вот медленный запрос:

SELECT * FROM `domain` WHERE `date_expire` < NOW() ORDER BY `last_check` ASC limit 0,10

Результат:

Отображение строк 0 - 9 (10 всего, Запрос занял 3.4408 сек.)
[last_check: 2015-11-20 14:30:03 - 2015-11-20 14:34:35]

В среднем 3-4 секунды.

P.S.
update
И что интересно, но тот же запрос с сортировкой DESC выполняется за 0.0003 сек.

SELECT * FROM `domain` WHERE `date_expire` < NOW() ORDER BY `domain`.`last_check` DESC limit 0 , 10

Отображение строк 0 - 9 (10 всего, Запрос занял 0.0003 сек.) [last_check: 2015-11-22 14:00:04 - 2015-11-22 14:00:03]

Comments

[svd71]

Нужно создать индекс на эти два поля.

[Алексей Уколов]

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

Answer 1

[Макс]

рецепт

Comments

[pcdesign]

Пока не понятно как по этому рецепту приготовить нашу кашу )

[Алексей Уколов]

pcdesign: Должно получиться что-то вроде этого:

SELECT * FROM domain d
INNER JOIN (
    SELECT `id` FROM `domain` WHERE `date_expire` < NOW() ORDER BY `last_check` ASC limit 0, 10
) tmp
ON tmp.id = d.id

И если у вас в date_expire хранится именно дата, без времени, имеет смысл заменить NOW() на конкретное значение, чтобы запрос закешировался.

[pcdesign]

Алексей Уколов: Ускорилось. В 3 раза быстрее:
Отображение строк 0 - 9 (10 всего, Запрос занял 1.3008 сек.)

Это максимум который можно выжать отсюда?
В поле date_expire хранится и дата и время, но время не носит принципиальный характер для этой выборки.

[Алексей Уколов]

Что-то удастся выиграть, если заменить NOW() на конкретную строку с датой, но думаю, что принципиально быстрее сам запрос уже не сделать.
Но если делать выборку раз в день и кешировать её (в файл, например), то время выполнения запроса перестанет быть проблемой.

[Алексей Уколов]

Хотя, у вас же не зря поле last_check есть - кешировать, видимо, не получится. В общем, если вы свою задачу опишете (желательно, отдельным вопросом), вам могут помочь с более оптимальным алгоритмом.

[pcdesign]

Алексей Уколов: Выборка делается каждую секунду. Но сейчас смотрю уже стало 0.5 секунды. Спасибо большое!
Если разместите свой ответ в отдельном посте, то отмечу и данный пост и ваш как решение.

[Алексей Уколов]

Отметьте этот ответ, я же от него отталкивался.

[pcdesign]

Алексей Уколов: Уже 0.3 секунды. Потихоньку уменьшается время, не понятно почему.

[Алексей Уколов]

А вы NOW() убрали? Если да, то это кеш начинал работать. Даже если не убрали, всё равно дело в кеше и нагрузке на сервер :)

[pcdesign]

Алексей Уколов: NOW() - не убирал. И я понял из-за чего ускорилось. Я вместо * указал нужные мне поля, если возвращаю * то опять 1.3 секунды.

[pcdesign]

Вот же ж век живи...

Answer 2

[IceJOKER]

перед запросом введите слово - EXPLAIN в результате вы увидите какие ключи были использованы и т.д. в общем полезная команда

Comments

[pcdesign]

Об этой команде я и сам знаю.
Толку то от этого.

MariaDB [expire]> EXPLAIN SELECT * FROM `domain` WHERE `date_expire` < NOW() ORDER BY `last_check` ASC limit 0,10;
+------+-------------+--------+-------+--------------------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+-------+--------------------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | domain | index | exp_and_last,date_expire | last_check | 4 | NULL | 20 | Using where |
+------+-------------+--------+-------+--------------------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [expire]>

[Алексей Уколов]

Как нет толка, если ясно видно, что используется неоптимальный индекс last_check?

[pcdesign]

Алексей Уколов: Да ладно. Тот же самый результат, если принудительно взять другие индексы

SELECT * FROM `domain` FORCE INDEX(exp_and_last) WHERE `date_expire` < NOW() ORDER BY `last_check` ASC limit 0,10

[Алексей Уколов]

И что explain выдает на такой запрос?

[pcdesign]

Алексей Уколов: Вот

MariaDB [expire]> EXPLAIN SELECT * FROM `domain` FORCE INDEX(exp_and_last) WHERE `date_expire` < NOW() ORDER BY `last_check` ASC limit 0,10
-> ;
+------+-------------+--------+-------+---------------+--------------+---------+------+--------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+-------+---------------+--------------+---------+------+--------+---------------------------------------+
| 1 | SIMPLE | domain | range | exp_and_last | exp_and_last | 9 | NULL | 911240 | Using index condition; Using filesort |
+------+-------------+--------+-------+---------------+--------------+---------+------+--------+---------------------------------------+
1 row in set (0.00 sec)

MariaDB [expire]>

[Алексей Уколов]

А вот эти 911k строк - это все, которые подпадают под условие `date_expire` < NOW()?

[pcdesign]

Вот что попадает под это условие:
MariaDB [expire]> SELECT count(*) FROM `domain` WHERE `date_expire` < now() ;
+----------+
| count(*) |
+----------+
| 855782 |
+----------+
1 row in set (0.44 sec)
=========================

[Игорь]

pcdesign: Ну и потом он эти 855782 сортирует по last_check и только потом выбирает из полученного 10 записей. Насколько я знаю, в данном случае ключ будет использоваться только один раз, либо про where либо при order.
Можно попробовать увеличить значения переменных sort_buffer и record_rnd_buffer

[pcdesign]

Игорь: Если сортировать по ASC -4 секунды. А если по DESC -0.003 секунды. Mysql индексы отсортировывает по убыванию и когда сортируем по возрастанию ему надо перелопатить тьму строк, операция трудоемкая для него. Не понимаю, что с этим делать.

[Игорь]

pcdesign: В других ДБ (firebird к примеру) можно указать направление сортировки ключа.
В вашем случае выборка по DESC происходит мгновенно ибо сортировка останавливается после первых 10 результатов. В случае ASC пробегается вся таблица :(
Попробуйте поиграть с серверными переменными. Либо изменить WHERE так, чтобы результатом была меньшая выборка.
Если есть возможность, дайте дамп таблицы, поэксперементирую

[Макс]

pcdesign: ну так возьмите не первые 10 при сортировке ASC, а последние - при DESC.

[pcdesign]

Макс: Мда. Смысл в том, чтобы выдергивать из поля `last_check` самые старые значения.

[Макс]

pcdesign: ну так получатся ровно те-же самые значения

[pcdesign]

Макс: Оригинально )). Попробовал для прикола сделать limit 800000,10. Результат те же самые 4 секунды.

[Алексей Уколов]

Смысл в том, чтобы выдергивать из поля `last_check` самые старые значения.

Для этого нужно знать количество результатов, иначе offset не сделать.