Есть таблица - 100 000 записей, MYSQL 4.0.24 
Делается запрос -
SELECT id,..... FROM users WHERE area='xxx' AND phone_preff='xxx' AND age BETWEEN x AND y ORDER BY timeregistered DESC LIMIT 0,10

Стоит key (area,phone_preff,age,timeregistered)
Поля - area - ENUM(1,2,3,....) phone_preff - smallint(3),  age -  smallint(3), timeregistered - int(10)

Проблема в том, что при запросе с  age BETWEEN ... Mysql не использует ключ и в результате - при запросе EXPLAIN - using where, using filesort 

Тогда как при изменении условия age BETWEEN x AND y на age=x все нормализуется и filesort исчезает, остается только  using where


При интенсивной работе базы нагрузка очень хорошо ощущается, пытался обойти каким либо образом, ни чего не помогает - вот варианты, 
вместо BETWEEN x AND y - 
age IN ('x','x1','x2',...)
еще обычный - age > x and age<y
Все равно - filesort!

а так же пытался использовать UNION - 



Однако при большом возрастном диапозоне этот запрос еще тяжелее предыдущихб хотя и не использует filesort 

Есть какое то более интересное и быстрое решение, оптимизировать запрос?

если бы ты сбросил структуру и маленький(на 8-10 строк  - можно прикрепить файлом к сообщению) дамп таблицы, можно было бы проэкспериментировать и быстрее найти решение. А касательно UNION - так у тебя запрос перед выдачей результата делает поиск, чтоб не было одинаковых строк. Если такого быть наверняка не может, то делай UNION ALL. впрочем, всё равно будет медленно, просто имей в виду на будущее.

Точно... Давай дамп... на 5-ке поэкспериментирую...

А ты не пробовал явно указать что использовать при выполнении запроса? USE INDEX/IGNORE INDEX 

И еще:Методы пассивной борьбы ищи также в мане по MySQL... может завести отдельный индекс по age?

Спасибо всем за ответы.
 
Akina, да, пробовал использовать USE INDEX(search)

когда  search - (area,phone_preff,age,timeregistered)
Не помогает!


Kesh, 
skyboy, Спасибо за совет с UNION ALL.  

Вот кусок таблицы со структурой - 



Интересно, если можно придумать обходной путь для выборки по возрасту....  ,
да - хостинг не позволяет использовать 5 мускул

Добавлено @ 11:11 
Akina,  пробовал завести отдельный индекс по возрасту - ноль результата - не используется

Походу дела решение нашлось - 
Создаем доп. ключ area (area,phone_preff,timeregistered)

И при запросе



Уже не используется filesort !!

Но почему, убирая age  из идекса мы получаем лучший результат, хотя по логике все должно быть наоборот?

Не, если завести и сказать USE INDEX age - все одно filesearch?

Akina, да, все одно, не использует... 

офтопик: читая эту тему проабгрейдил один из своих скриптов. Спасибо!

Вам дали почти правильный совет по поводу использования конструкции USE INDEX. Но USE носит рекомендательный характер для mysql. Есть более "сильный" вариант: FORCE INDEX () (работает с 4.0.9 версии), mysql будет использовать данный в скобках индекс, если это возможно для выполнения запроса.

А по поводу того, почему игнорируется индекс при задании рэнджа для age:
При выборе способа отработки селект-запроса MySQL решает, целеобразно ли ему использовать какой-либо индекс. По моим наблюдениям, отрицательное решение принимается в случае, когда кол-во результирующих строк будет больше десятка процентов от кол-ва записей в индексе. Т.е. в вашем случае для age индекс используется только тогда, когда ему задают конкретное значение (скорее всего, если написать age between 25 and 26, индекс всё-таки тоже будет использоваться, т.к. такой рэндж охватывает относительно малое кол-во строк).
Почему MySQL не решает использовать только первую часть индекса - загадка природы разработчиков этой БД 


Это сообщение отредактировал(а) muzer - 31.8.2006, 21:26

По моим наблюдениям, мускуль использует либо индекс целиком, либо вообще не использует.

Кстати, у меня была подобная проблема, но наличие filesort'а там было критично ( ndbcluster не дружит с оным ), посему было решено забить на сортировку и сделать её на клиенте.


За это спасибо 

Нет, ну почему же, левую часть индекса он умеет использовать:

Обратите внимание на key_len, в первом случае весь индекс (2 инта), во втором - только левая часть.

shurale, попробуй еще вот такой запрос


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

Это сообщение отредактировал(а) S.A.P. - 3.9.2006, 14:55

Интересное решение, не могу его только опробовать - что то в синтаксисе, какая то ошибка, а я не очень силен в синтаксисе вложенных запросов. Но мысль интерсна - сократить диапозон, и по нему уже делать сортировку.

Нет, это он не может делать...


Действительно еще одна загадка   
Использовать или не использовать выбираеся оптимизатором по среднепотолочной системе?