При укзании в запросе значений для функции IN() больше 4, индексы не используются.

[Гриша К.]

При укзании в запросе значений для функции IN() больше 4, индексы не используются.

Здравствуйте.

# Есть следующая таблица:
CREATE TABLE products (
  product_id int(10) unsigned NOT NULL auto_increment,
  category_id int(10) unsigned NOT NULL,
  product_name char(255) NOT NULL,
  product_description text NOT NULL,

  PRIMARY KEY  (product_id),		#Длина 9
  KEY category_id (category_id),	#Длина 9
) ENGINE=InnoDB;

Например, таблица содержит 9 записей, для которых category_id имеет значения 13, 23, 59, 137 (т.е., если GROUP BY category_id, то кол-во строк 4)

# Есть запрос к БД:
SELECT											
     * # Перечень нужных столбцов											
FROM 
	products				
WHERE
	category_id IN(0,56,59,137,138,140)			
	LIMIT 0, 5

# При описаниии запроса (EXPLAIN) результат такой:
id | select_type |  table   | type | possible_keys | key  | key_len | ref  | rows | Extra  
1       SIMPLE     products    ALL    category_id    NULL     NULL    NULL    9     Using where

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

# Если в условие запроса (WHERE) добавить "product_id > 0",
# в результате EXPLAIN такой (даже если LIMIT 0,15 и в результате кол-во извлекаемых записей 7):
id | select_type |  table   | type  | possible_keys 	    | key     | key_len | ref  | rows | Extra  
1       SIMPLE     products   range   PRIMARY, category_id    PRIMARY      4      NULL    4     Using where

Если в условие запроса (WHERE) указать количество значений для функции IN (0,56,59,137),
соответвующее общему кол-ву значений category_id в таблице (13, 23, 59, 137 - 4 разных значения),
то EXPLAIN будет как предыдущий, только rows = 7, а если LIMIT 0,15, то индексы не будут использоваться (type = ALL).

Пробовал указывать
USE INDEX(PRIMARY,category_id), USE INDEX(category_id) - type = ALL,
FORCE INDEX(category_id) - type = range, key = category_id, но записи просматриваются веравно все (9)

Подскажиет пожалуйста как правильно оптимизировать запрос? Есть ли услвовие "product_id > 0", оптимальным и верным решением?
И мне непонятно почему при описанных выше условиях, такая разница в описании запроса EXPLAIN

 

[chira]

Для твоих девяти записей самым оптимальным будет прочитать всю таблицу и не использовать индексы.

 

[Гриша К.]

chira, Спасибо за ответ.
В данном случае 9 записей это пример, предположим что в таблице товаров будет 10 000 записей, именно для данного запроса насколько я понимаю это будет совем нестрашно.
Но если эта таблица будет объединяться с другими, и из другой таблицы будут также извлекаться все записи (ну пусть их будет 100), в результате MYSQL должен будет просмотреть 1,000,000 строк.
Вот пример запроса: http://phpclub.ru/talk/showthread.php?postid=653223#post653223
хотя возможно это проблема запроса в целом.

И все таки мне непонятно почем данный вариант будет оптимален, а если я укаже в IN количество значений меньше 4, то почему-то тогда используется индекс. Как вы это можете объяснить?

 

[.des.]

Вам же уже объяснили, что оптимизатор MYSQL решает, что быстрее table scan, чем чтение индекса, а потом значительной части записей из таблицы.
До совсем недавнего времени индекс не использовался если MYSQL необходимо было прочитать больше чем 30% записей таблицы, сейчас логика более сложная.
Подробнее здесь:

http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html

 

[Гриша К.]

.des., спасибо за разъяснение и ссылку.
Пользователя chira я непонял, вы мне разъяснили, я понял.

У меня есть ссылка на документацию MySQL на русском, и глазами пробегал ту часть документации, а сейчас нашел ее и прочитал подробнее http://hosting.skif.net/docs/mysql/manual.ru_toc.html#Where_optimisations.
Когда заполню таблицы большем количеством строк, буду смотреть еще раз EXPLAIN.

 

[vovik]

Если в таблице будет 10 000 записей, то и оптимизировать запросы надо на 10 000 записях, а не на 10.

Что касается приведенных примеров, не надо думать, что если в эксплейне есть волшебное слово ИНДЕКС, то запрос будет обязательно работать быстрее, чем без него. Конкретно для запросов из примера первый эксплейн будет я думаю быстрее второго.

Потому что во втором случае придется пройти все записи индекса (условие product_id > 0 неселективно вообще), для каждого значения в индексе выбрать запись из самой таблицы и сравнить category_id. Быстрее будет сразу сканировать таблицу.

 

[Гриша К.]

vovik, спасибо за разъяснение,
а смотрю, почему то запрос product_id > 0 выполняется медленнее на 0.001-0.002 секунды.
И неправильно я действително поступил, надо мне сначала заполнить БД, потом тестировать и уж тогла вопросы на форум.
Но разъяснения я получил всеравно хорошие, спасибо всем.
Буду заливать БД, тестировать.