SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 05.08.2017 23:00:08

inmerdorm
Участник
Зарегистрирован: 12.08.2014
Сообщений: 24

Оптимальный запрос для выборки

Здравствуйте.
Помогите, пожалуйста, написать оптимальный с точки зрения скорости выполнения запрос на выборку строки из таблицы.
В таблице есть несколько столбцов: id, дата и прочие. В этой таблице несколько десятков тысяч записей.
Нужно найти последнюю запись к указанной дате. Поясню: Пользователь вводит дату. Например, 1 Августа такого-то года.
Нужно, чтобы были найдена либо последняя запись для данного дня (т.е. если в этом дне много записей, то взять запись с наибольшим id), либо, если за текущий день нету записей - за самый ближайший предыдуший день, в котором есть записи.

Неактивен

 

#2 05.08.2017 23:32:26

inmerdorm
Участник
Зарегистрирован: 12.08.2014
Сообщений: 24

Re: Оптимальный запрос для выборки

p.s.
Ну, у меня допустим так:

SELECT * FROM `table1` where `date` <= '2014-01-03' ORDER BY `id` DESC LIMIT 1

Но эффективно ли это? Может, как-то по-другому надо?

Неактивен

 

#3 06.08.2017 08:52:08

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Оптимальный запрос для выборки

Все правильно, если не устроит быстродействие  можно добавить индекс по дате (скорее всего нужно).
Но это будет верно работать только если ID идут по возрастанию.

Отредактированно klow (06.08.2017 08:53:03)

Неактивен

 

#4 06.08.2017 11:22:30

inmerdorm
Участник
Зарегистрирован: 12.08.2014
Сообщений: 24

Re: Оптимальный запрос для выборки

klow написал:

Все правильно, если не устроит быстродействие  можно добавить индекс по дате (скорее всего нужно).

А как это делается?

klow написал:

Но это будет верно работать только если ID идут по возрастанию.

Автоинкрементом, но многие строки удалены. Но всё равно всегда по возрастанию.

Неактивен

 

#5 06.08.2017 11:28:58

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Оптимальный запрос для выборки

inmerdorm написал:

А как это делается?

mysql> CREATE INDEX table1_index_date ON table1(`date`);

inmerdorm написал:

Автоинкрементом, но многие строки удалены. Но всё равно всегда по возрастанию.

Удаление - не страшно.

Неактивен

 

#6 06.08.2017 15:09:15

inmerdorm
Участник
Зарегистрирован: 12.08.2014
Сообщений: 24

Re: Оптимальный запрос для выборки

klow, спасибо за ответ.

Кол-во строк:
http://i.imgur.com/AWuiVK8.png
Без индекса:
http://i.imgur.com/bVSampr.png
С индексом:
http://i.imgur.com/cgfBcE0.png

Говорит ли результат о том, что я зазря заморачиваюсь?


p.s. какой движок должен быть для такой таблицы? я когда её сто лет назад создавал, выбрал InnoDB по неглубокому анализу гугловыдачи. Однако я, как не специалист, не уверен в своём выборе.

Отредактированно inmerdorm (06.08.2017 15:19:07)

Неактивен

 

#7 07.08.2017 13:58:55

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Оптимальный запрос для выборки

В данном случае это время не выборки данных, а результата EXPLAIN.
Индекс рекомендуется, но полный ответ зависит и от других условий. Например, если это выборка будет раз в день и ее время не существенно, тогда можно и без индекса, для экономии (сомнительной) места на диске. Но я бы не заморачивался и делал бы индекс.

InnoDB  - выбор правильный для большинства случаев.

Отредактированно klow (07.08.2017 13:59:37)

Неактивен

 

#8 07.08.2017 23:24:36

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Оптимальный запрос для выборки

Со скоростью пули тут бы отработал индекс на связку (`date`,`id`),
но, увы, полностью индекс будет задействован только если будет условие равенства на `date`, а не <=.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#9 08.08.2017 14:05:41

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Оптимальный запрос для выборки

но, увы, полностью индекс будет задействован только если будет условие равенства на `date`, а не <=.

Думаю будет, и второй рисунок тому подтверждение. Может имелось ввиду DATETIME?

Отредактированно klow (08.08.2017 14:07:49)

Неактивен

 

#10 08.08.2017 14:08:39

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Оптимальный запрос для выборки

Если ТС приведет explain'ы на запросы

SELECT * FROM `table1` where `date` <= '2014-01-03' ORDER BY `id` DESC LIMIT 1;

и
SELECT * FROM `table1` where `date` = '2014-01-03' ORDER BY `id` DESC LIMIT 1;


то мы должны увидеть разницу smile.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#11 09.08.2017 00:37:50

inmerdorm
Участник
Зарегистрирован: 12.08.2014
Сообщений: 24

Re: Оптимальный запрос для выборки

klow написал:

но, увы, полностью индекс будет задействован только если будет условие равенства на `date`, а не <=.

Думаю будет, и второй рисунок тому подтверждение. Может имелось ввиду DATETIME?

нет, просто date.

deadka написал:

Если ТС приведет explain'ы на запросы

SELECT * FROM `table1` where `date` <= '2014-01-03' ORDER BY `id` DESC LIMIT 1;

и
SELECT * FROM `table1` where `date` = '2014-01-03' ORDER BY `id` DESC LIMIT 1;


то мы должны увидеть разницу smile.

http://i.imgur.com/6zYAMjy.png


klow написал:

В данном случае это время не выборки данных, а результата EXPLAIN.

Да всё равно ноль показывает.

Отредактированно inmerdorm (09.08.2017 00:38:17)

Неактивен

 

#12 09.08.2017 07:33:16

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Оптимальный запрос для выборки

deadka написал:

Со скоростью пули тут бы отработал индекс на связку (`date`,`id`),
но, увы, полностью индекс будет задействован только если будет условие равенства на `date`, а не <=.

Я говорю про использование индекса именно на связку (date,id). Что он будет использоваться полностью и работать очень быстро - в случае если на дату будет строгое, а не нестрогое равенство.
А приведенный explain использует PRIMARY KEY, подозреваю, это на поле id.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#13 09.08.2017 07:38:23

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Оптимальный запрос для выборки

делать связанный индекс, бессмысленно, так как уже есть индекс на ID. Достаточно только по date.

Неактивен

 

#14 09.08.2017 07:55:42

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Оптимальный запрос для выборки

klow написал:

делать связанный индекс, бессмысленно, так как уже есть индекс на ID. Достаточно только по date.

Два индекса одновременно использоваться не будут, здесь не PostgreSQL smile. Так что вполне осмысленно.
Но разница в скорости будет заметна только на больших объемах данных, и если много id соответствует одной дате.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#15 09.08.2017 09:06:05

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Оптимальный запрос для выборки

Понятно, что в данной выборке не будут использоваться два индекса, но существовать в базе будут два индекса на основе одного поля. В этом нет смысла. Возможно, это имеет смысл только на очень больших объемах, но в данном случае это бессмысленно. Есть индекс по ID и достаточно по DATE, делать индекс составной (DATE, ID) нет никакого смысла в таком случае.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson