Mysql запрос выполняется 17 секунд. Как оптимизировать?

Question

[Алексей]

Всем привет.

Есть такой запрос:

SELECT waterTemp_C, observation_time_date FROM weatherArchiveDay WHERE placeID=266 AND observation_time_month=11 ORDER BY waterTemp_C DESC LIMIT 1

Изначально работал ок, но теперь когда 4 миллиона записей тормозит жутко )

Суть запроса - выбирается у определенного города (placeID) в определенный месяц (observation_time_month) самую низкая температура waterTemp_C.

Попробовал использовать встроенные функции напр min

SELECT min(waterTemp_C), observation_time_date FROM weatherArchiveDay WHERE placeID=266 AND observation_time_month=11

Теперь летает, и температуру правильную выдает, но дату первую по Primary ID, а не ту, когда такое температурное значение.

Подскажите пжста как скорректировать запрос. Спасибо!

Comments

[FanatPHP]

Про индексы слышал когда-нибудь?

[Slava Rozhnev]

Опубликуйте версию базы данных, структуру таблицы + индексы

[Slava Rozhnev]

В подобных случаях можно использовать Partitioning

[FanatPHP]

Slava Rozhnev, не смеши мои тапочки
Ты конечно крутой чувак, но у тебя тенденция к оверинжинирингу.
Какое еще партиционирование для таблицы размером в пару десятков мегабайт?

[Алексей]

забыл написать индексы есть, может их много..

observation_time_date_indx
placeID_indx_weatherArchiveDay
observation_time_month_indx_weatherArchiveDay
temp_C_day_indx_weatherArchiveDay
temp_C_night_indx_weatherArchiveDay
waterTemp_C_indx_weatherArchiveDay
dayDuration_indx_weatherArchiveDay

просто по всем этим полям нужны минимальные и максимальные значения

[Slava Rozhnev]

FanatPHP, Смотри, я понимаю речь идет о хранении показателей неких датчиков. Такие таблицы имею свойство быстро расти. Поэтому возможно заранее подготовить архитектуру. Снова, так как мне не известны детали, речь идет об одном из возможных вариантов.

[Алексей]

все уже сделано. Мужики что сейчас то делать? ) я думал запрос как-то оптимизировать

[FanatPHP]

Хорошо, значит слышал.
Давай подумаем, какой индекс подошел бы для этого запроса? Не из этих, а вообще. Какой индекс ты бы для него составил?

[Алексей]

FanatPHP, именно для этого запроса нужен идекс на город и месяц (placeID и observation_time_month).. но они есть. Как правильно то? Можно конечно спаренный индекс placeID+observation_time_month

но мне бы если честно уже подсказали как исправить, а то одна эскуэль философия )

[FanatPHP]

не можно а нужно.
БД может использовать только один. так что наличие второго вообще ничем не помогает

И заодно в этот индекс нужно добавить и другие данные, которые возвращает запрос, чтобы он читал из индекса, а не с диска.

[Алексей]

т.е. получается создать тройные индексы для каждого искомого поля? Напр

temp_C_day +placeID+observation_time_month
temp_C_night +placeID+observation_time_month

так?

[Алексей]

сделал пока на два поля placeID+observation_time_month - уже летает )

Answer 1

[Владимир Коротенко]

Постройте план исполнения, что это ищите в интернете, проанализируйте его, добавьте индексы, навскидку просто создайте те которые у вас в поиске и сортировке.

Comments

[FanatPHP]

Или пиши нормально, без "ну найдешь в гугле" или пиши не ответ, а комментарий.
Плюс не пиши ерунды.

Я не стал уж влезать в твой вопрос как объяснять нормально, поскольку там уже не про тебя лично речь шла, но конкретно для тебя ответ простой - надо самому понимать то, что ты пытаешься другим объяснять.
А с этим у тебя очень большие проблемы. Какое ещё "создайте индексы которые у вас в поиске и сортировке"? Сколько, по-твоему, нему здесь нужно индексов?

[Владимир Коротенко]

FanatPHP, placeid observation time, water temp. Найти же explain любой сможет. Впрочем индексы сверху могут быть избыточными нужно смотреть на план исполнения

[FanatPHP]

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

Answer 2

[Adamos]

Суть запроса - выбирается у определенного города (placeID) в определенный месяц (observation_time_month) самую низкая температура waterTemp_C.

И часто у вас меняется самая низкая температура в прошлом октябре?

Comments

[FanatPHP]

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

[Adamos]

FanatPHP, тут еще вторая идея о том, что часто у программиста есть болезненное стремление все сделать за один запрос, как бы мучителен он ни был по сравнению с двумя - простейшими, легкими и вообще выдающими данные из кэша БД.

[FanatPHP]

денормализация - это, по сути, кэширование
а кэширование никогда не бывает бесплатным. инвалидация кэша - это одна из двух величайших проблем информационных технологий.

[Adamos]

FanatPHP, в теории все понятно. На практике, если это метеорологические записи, то обновление данных идет отнюдь не тысячами в секунду. Должен быть простор для инвалидации кэша. Зато обработка данных, которая намного востребованнее, чем сохранение, резко ускорится.

[FanatPHP]

ну вот когда у него будет 400 миллионов записей - тогда да
а пока ему надо научиться с индексами работать
без индексов у него и денормализованная база колом встанет.
1000 мест, 120 месяцев за 10 лет наблюдений - файлсорт уже будет не сахар