Доброго времени суток!
У меня имеется 2 таблицы: элементы и значения свойств этих элементов.


Хочу отсортировать по полю присоединённой таблицы:


Задумывается очень-очень крепко...

Вот, что говорит explain:


Подскажите пожалуйста, как оптимизировать?

А если так?

Akina,
Пробовал - 51 сек. 
 По рекомендации http://www.mysql.ru/ перенёс всё это дело в WHERE.

А если в поддающихся сравнению единицах?

Хм... Сам не понял, что повлияло на результаты, но таблицы и индексы остались прежними, а время выполнения запросов 16.55684 мой вариант и 17.43842 Ваш.

Наверно, вся беда в Using temporary; Using filesort?

Не эквивалентно же!!!

Попробуйте составной индекс по properties (property_id,value) 
И если он сам не поймет в какой последовательности правильно джойнить - попробуйте определить порядок соединения непосредственно в запросе. /*к сожалению я не знаю как подсказать масиному оптимизаторов, что последовательность жойнов следует выдержать именно в порядке, определенном запросом*/



Это сообщение отредактировал(а) Zloxa - 5.9.2010, 13:16

Да как обычно - расставить скобки.

угу, есть такое

Я это к тому, что Оракля, к примеру, чхал на скобки, для него хинтовать приходится, чтоб он этого не делал ;)
А как дела в масе обстаят, ей богу не знаю.

Zloxa, 14.31255 сек.
EXPLAIN:

Если я не ошибаюсь, то сортировать, используя индекс, он не будет, потому что поле value имеет тип TEXT. Как-нибудь можно избавиться от filesort?



А нескромный вопрос... Почему не эквивалентно?

Это сообщение отредактировал(а) DCoder - 5.9.2010, 16:17

К сожалению я так и не научился читать масин эксплайн.
Индекс какой используется - ind_prop_pid_value?
Похоже - да.

потому что критерии отбора перенесены в критерии объединения.
Запрос, переписанный Akina вернет ВСЕ элементы, а Ваш только те, для которых определены свойства. 

тут не знаю. Весьма вероятно. пусть знающие подскажут.

Заменил тип данных поля value с TEXT на varchar(255) - время выполнения скрипта 0.19693 сек.
EXPLAIN:

Но! Стоит увеличить длину varchar(500), как индекс уже не используется, и возвращается filesort. Кто-нибудь может внести ясность?

не связано ли это с ограничением длины строки, хранимой в индексе?

тут.
Если индекс хранит не строку целиком, а лишь ее префикс, индекс не может быть использован для сортировки.

ЗЫ И, конечно, я проглядел что у Вас value имеет тип TEXT. Для меня наличие необходимости сортировки по значению поля этого типа - разрыв шаблона. Быть может стоит подумать о суррогатном поле, которое хранило бы префикс строки, заполнялось бы из триггера, было бы проиндексировано и использовалось бы для сортировки?

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

а что, кстати означает text(10)?
1) что поле вмещает лишь 10 символов
2) что в таблице хранятся первые 10 символов, а остальные хранятся гдето с боку

Ни то, ни другое. Это означает, что следует использовать наименьший из типов семейства TEXT, который вмещает указанное количество символов. В данном случае это будет TINYTEXT, макс. длина которого 255 символов.

Zloxa, 
Akina, 
Извиняюсь за долгое отсутствие!

Структура немного изменилась, и я думал, что уже вроде всё заработало, но тут у меня возник вообще ступор   
Может сегодня я уже заработался или звёзды не так сошлись... Создаю уже такой дубовый пример:


Вот что заявляет мне explain:


Возможных ключей нет, и ключ не используется!!! Как это понимать? Я уже несколько часов с ним воюю...
Если подставить FORCE INDEX (ind_int_field1), то он его будет использовать, но если попробовать к этой таблице присоединить какую-нибудь другую, то даже FORCE INDEX ему не указ.

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

DCoder, добавь миллион записей. И тогда индекс будет использоваться.

Akina,
У меня не то, чтобы миллион, но 300 000, я думаю, достаточно. А в possible keys он разве не должен быть в любом случае? Ещё одно наблюдение: если добавить какое-нибудь условие в WHERE по данному полю, то сразу подключается индекс.

Хороший вариант. Но я конвертировал данное поле в varchar(30) и создал индекс по всей длине. Примерно то же самое получил, но когда стоит один ORDER BY без каких-либо ограничений в WHERE, индекс не цепляется.

С этим полностью согласен, а насчёт  можно поподробнее?  Тут вроде не подходит под мой случай

Это сообщение отредактировал(а) DCoder - 11.9.2010, 00:05

C чего бы? оптимизатор - не идиот.

это ответ на  

Аааа... У меня мой бредогенератор вставлял в запрос SQL_CALC_FOUND_ROWS - я про него совсем забыл. Поэтому индекс и не использовался.
Всем спасибо за помощь!