MySQL неверно выбирает индекс в запросе

[Rin]

MySQL неверно выбирает индекс в запросе

Есть таблица в БД (MySQL-5.0.45-community-nt):

CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `page_id` int(10) unsigned default NULL,
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `created` (`created`),
  KEY `page_id` (`page_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;


/*!40000 ALTER TABLE `t` DISABLE KEYS */;
INSERT INTO `t` (`id`,`page_id`,`created`) VALUES
 (1,1,'2007-10-28 19:36:15'),
 (2,3,'2007-10-28 19:37:03'),
 (3,3,'2007-10-28 19:37:08'),
 (4,3,'2007-10-28 19:37:09'),
 (5,3,'2007-10-28 19:37:09'),
 (6,3,'2007-10-28 19:37:10'),
 (7,3,'2007-10-28 19:37:10'),
 (8,3,'2007-10-28 19:37:11'),
 (9,3,'2007-10-28 19:37:11'),
 (10,3,'2007-10-28 19:37:16'),
 (11,3,'2007-10-28 19:37:17'),
 (12,3,'2007-10-28 19:37:17'),
 (13,3,'2007-10-28 19:37:17'),
 (14,3,'2007-10-28 19:37:20'),
 (15,3,'2007-10-28 19:37:21');
/*!40000 ALTER TABLE `t` ENABLE KEYS */;

В этом запросе оптимизатор MySQL неверно выбирает индекс, что плохо сказывается на скорости выборки на большом кол-ве рядов.

EXPLAIN
SELECT *
FROM t -- USE KEY (created) -- правильный ключ
WHERE page_id = 5
ORDER BY created DESC
LIMIT 5;

Замечу, что подобные запросы, где есть условие WHERE и ограничение LIMIT обычно наиболее используемые при разработке веб-сайтов (постраничный вывод каких-либо списков: новости, форум, ...).

Думаю, что есть способ научить оптимизатор правильно выбирать индекс в подобных запросах.
Интересно, как обстоит дело с выбором индекса в других БД?
Если там все хорошо, я напишу разработчикам.

 

[Krishna]

EXPLAIN SELECT * FROM t -- USE KEY (created) -- правильный ключ WHERE page_id = 5 ORDER BY created DESC LIMIT 5;

Где вывод результатов EXPLAIN?

-~{}~ 28.10.07 22:35:

Полагаю используется page_id?
А хотелось бы created?

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

...

#The key used to fetch the rows is not the same as the one used in the ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html

Мог бы и сам посмотреть.

 

[Rin]

Спасибо за ссылку. Получается, что пока разработчики не торопятся улучшать ситуацию...

 

[kruglov]

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

 

[Dovg]

Мы пару дней назад за кружечкой чая тестировали свою базу - две таблицы, в одной 150 записей, в другой 800 000
навешивали кучу всяких индексов, просто ради тестов.

MySQL ВСЕГДА использовал быстрейший индекс, т.е. при явном указании USE (другой индекс) скорость заметно падала.

на тестовой машинке была 5.0.37 под винду.
Или нам везло или база на самом деле умная

 

[Rin]

kruglov

Составной индекс в виде
KEY `index_4` USING BTREE (`page_id`,`created`)
использовать не имеет смысла.

А составной индекс в виде
KEY `index_4` USING BTREE (`created`,`page_id`)
автоматически не выбирается.

Dovg
Если так, то Вам просто повезло.

 

[Gas]

Составной индекс в виде
KEY `index_4` USING BTREE (`page_id`,`created`)
использовать не имеет смысла.

Для запроса в первом посте? Как раз это оптимальный индекс для него.

 

[kruglov]

Составной индекс в виде
KEY `index_4` USING BTREE (`page_id`,`created`)
использовать не имеет смысла.

В вышеприведенном мануале написано, что именно так и имеет смысл.
Правда, не знаю, что там насчет USING BTREE...

 

[Gas]

BTREE индекс и даёт возможность range условий и сортировок, в отличие от hash.

 

[Rin]

всем участникам дискуссии
Если предположить, что 90% записей будут иметь `page_id` = 3, а `created` 90% уникальных значений, сколько записей придется просмотреть MySQL в вышеприведенном запросе при использовании составного ключа KEY `index_4` USING BTREE (`page_id`,`created`) ?

 

[Gas]

Explain не показывает влияние limit'а.
Имхо, даже если 90% записей page=3, то при твоём запросе и ключе (page_id,created) mysql должен просмотеть количество записей, равное значению в limit'е.

 

[Rin]

Gas
Согласен.
Но все-таки составной ключ здесь лишний, если использовать USE KEY (created)...