Здравствуйте. Есть запрос:


Структуру таблиц, думаю, приводить нет смысла. Все суммируемые колонки unsigned int.
Время выполнения запроса -- 40 секунд. Профайлер показывает что все это время расходуется на копирование в tmp таблицу. 
Можно ли уменьшить время выполнения запроса?

explain для запроса:

  индексы посмотреть может не срабатывают, может стоит создать тестовую базу и протестировать с разыми вариантами...?

Индекс не используется для where по TilesAttributes.extractDate >= '2009-07-02' (почему я так и не понял, но суть в том, что сама выборка занимает ничтожно малое время). Видимо MySQL выбирает full scan из-за малой селективности индекса и большого колическтва данных, подходящих под условие (из 3*10^6 строк подходят 2.5*10^6).
А разные варианты я, естественно, пробовал -- результата пока нет. Получалось тольку увеличивать время на 2 секунды ))

это:


как то не сходится с этим:


и что за tmp таблица у вас, в скрипте про нее ничего не сказано.

еще как сходится. В процессе выполения запроса данные не только выбираются, но и сортируются, группируются и т.д.

о ней сказано в explain -- результат выполнения запроса (или его части) mysql размещает во временной таблице. В идеале это таблица с engyne memory, но если не хватает мета в памяти, то данные сбрасываются на диск в myisam таблицу.

где? ткните пальцем.



вы что действительно считаете что результатом милионной выборки является засранная память и tmp таблица на диске? если не трудно ссылочкой поделитесь где описан этот процесс.


а сортировка по null это фича mysql?  

А вы считаете что данные , с которыми работает сервер размещается в небесном эфире, а не в оперативке?
Четко описанного процесса выполнения запроса сейчас не найду, но подверждение своих слов:
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html


Это борьба с фисей MySQL. MySQL по умолчанию сортирует данные по столбцу группировки (т.е. к using temparary добавится еще и filesort). Ну а order by null позволяет этого избежать

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


извиняюсь что влезаю с вопросами в вашу тему, просто хочу пронять - это борьба с сортировкой до group by(имеется ввиду сортировка вспомогательная для group by) или mysql типа так заботится о разработчике пологая, что сгруперованные данные понадобятся сортированными?


ага, увидел.


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

Добавлено через 9 минут и 17 секунд

сори за формулеровку, имел ввиду не ВЕРНУТЬ, а НАЧАТЬ ВОЗВРАЩАТЬ.

после group by. 
//Мне тоже иногда кажется, что разработчики слишком заботливы )

это непосредственно время выполнения (отчет о времени по данным самого сервера). Прикрепил полный профайл запроса.

Присоединённый файл ( Кол-во скачиваний: 1 )
 query_profile.txt 9,13 Kb

И всё=таки дай запрос формирования таблиц, а? достаточно оставить только значимые поля и существующие индексы. 
Тогда можно просто залить его в БД и покрутить. А так, на пальцах, муторно... А воссоздавать просто влом.

прикрепил sql с структурой

Ну и данные по количеству строк в таблицах:


Присоединённый файл ( Кол-во скачиваний: 4 )
 table_structure.sql 3,44 Kb

Не совсем Вы правильно поняли.
Чем выше селективность предиката отбора, тем  больший процент данных данных возвращается запросом. При селективности предиката отбора = 1 возвращается весь набор данных. По предикату с селективностью = 0 не будет произведено отбора вовсе.
Соответственно Вы имеете предикат с высокой селективностью, не с низкой.
В вашем случае селективность = 0,83(3) и это действительно очень большая селективность и индексный доступ не будет эффективным.
Однако это лишь пол беды.
У Вас ведь есть еще один предикат отбора - CountryID = '2', о селективности которого Вы умолчали.
В альтернативном плане, можно было бы использовать как лидирующую таблицу отбор из Metro по CountryId = 2, подтянуть Tiles и, следом отфильтроваться по TilesAttributes.
Однако этот план не осуществим изза того что критерий объединения использует функцию:

По этому критерию Tiles не может быть ведущей для TilesAttributes, только ведомой.

Ну и еще замечание. В Вашем запросе критерии отбора применяются к соединенным полям. Это приводит к тому, что все outer join, прописанные в запросе вырождается в inner. Думаю оптимизатор это понимает и Вам не удалось его запутать. Но чтобы не запутаться самому и не запутывать тех, кто будет в последствии разбирать Ваш запрос, было бы неплохо заменить outer join на inner, там где он таковым является на самом деле.

Это сообщение отредактировал(а) Zloxa - 31.8.2009, 09:23

Понял то правильно, авот в терминах (малая эффективность/малая селективность) напутал.





А есть где-то описание всего списка критериев, по которым таблица (не)может быть ведущей?


Да, тут inner и по логике, и по сути. Естественно будт изменены. Спасибо.

Добавлено через 7 минут

Я таки идиот. Добавил TileID6 -- первые шесть символов из tileId и создал по нему индекс:

Время выполнения 2 секунды.

Спасибо. Но по поводу ведущих/ведомых таблиц вопрос остается актуальным.

Честно говоря не уверен, что это гдето явно описано, но логика ведь проста. Tiles.TileID может быть получен из TilesAttributes.tileId однако вычислить TilesAttributes.tileId из Tiles.TileID  невозможно, потому Tiles не может быть ведущей.

Существуют рекомендации(так называемые Best Pracrtice) по возможности не использовать функции от значений полей в критериях объединения и отбора

Это сообщение отредактировал(а) Zloxa - 31.8.2009, 10:19

Я тоже долго ржал когда впервые вычитал. Полагаю изначально группировка могла выполняться только через сортировку, потому сортировку при группировке задокументировали и даже добавили модификаторы ASC, DESC. А потом пришлось тянуть функционал для обратной совместимости и придумывать нелепые воркэраунды, чтобы избежать оверхеда.