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

Perfilev · 15 Янв 2011

Ситуация такая: есть табличка с 700,000 записей.

PHP:

CREATE TABLE IF NOT EXISTS `icons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `ssId` int(10) unsigned DEFAULT NULL,
  `imported` tinyint(1) NOT NULL default '0',
  `userId` int(10) NULL DEFAULT NULL,
  `status` set('deleted','pending','approved') default "pending",
  `dateProcessed` datetime NULL default NULL,
  `title` text NOT NULL,
  `previewUrl` varchar(512) NOT NULL,
  `tags` text NOT NULL,
  `json` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `SUI` (`status`, `userId`),
  KEY `status` (`status`),
  KEY `userId` (`userId`)
) ENGINE=InnoDB;

Делаю такой запрос:

PHP:

SELECT *
FROM `icons`
WHERE `status` = "pending" AND (`userId` IS NULL OR `userId` = 1)
ORDER BY `id` ASC
LIMIT 1

С ордером выполняется 25 секунд, без него моментально.

С ордером:

PHP:

id 	select_type 	table 	type 	possible_keys 	
1 	SIMPLE 	icons 	ref_or_null 	SUI,status,userId 	

key 	key_len 	ref 	rows 	Extra
SUI 	7 	const,const 	270075 	Using where; Using filesort

Без ордера:

PHP:

id 	select_type 	table 	type 	possible_keys 	
1 	SIMPLE 	icons 	ref 	SUI,status,userId 	

key 	key_len 	ref 	rows 	Extra
status 	2 	const 	338774 	Using where

Если сделать

KEY `SUI` (`status`, `userId`, `id`),

То получаем

PHP:

id 	select_type 	table 	type 		possible_keys 		
1 	SIMPLE	 	icons 	ref_or_null 	SUI,status,userId 	

key 	key_len 	ref 		rows 		Extra
SUI 	7 		const,const 	344665 	Using where

Запрос выстреливает моментально, но не возвращает результатов, видимо, из-за (`userId` IS NULL OR `userId` = 1), т.к.

PHP:

SELECT *
FROM `icons`
WHERE `userId` IS NULL AND `status` = "pending"
ORDER BY `id` ASC
LIMIT 1

выполняется опять же моментально и возвращает результат.

Что я делаю не так?

Perfilev · 15 Янв 2011

Пробовал, не помогло.
Ну и по-хорошему, это не решение

Perfilev · 15 Янв 2011

Пробовал тоже, такой не заработал.
Попытаюсь щас mysql обновить ещё, авось баг какой был)

Вурдалак · 15 Янв 2011

Чё ты так упорно ему советуешь id, status, userid? Почему такой порядок?

Вурдалак · 15 Янв 2011

Если я не ошибаюсь, то в каких-то версиях MySQL у таблиц типа InnoDB действительно была проблема с PK в подобных индексах.

zerkms · 15 Янв 2011

Так что дурит нас автор топика или его дурит mySQL...

или просто настало время для ANALYZE + OPTIMIZE

zerkms · 15 Янв 2011

. Так вот, подсознание подсказало правильное решение для второго случая - force index(primary)

Какой бред. Если мускул решил, что совпадений слишком много - значит их слишком много. Значит фуллскан будет выполнить быстрее, чем миллиард сиков по датафайлу.

Gas · 15 Янв 2011

Я бы пробовал индекс (status,id), userId в индекс добавлять для этого запроса смысла нет - он использоваться не будет из-за OR.
Если ужхочется чтоб и все части условия использовали индекс + сортировка, то стоит попробовать индекс (status,userId,id) или (userId,status,id) - нужно прикидывать по чём селективность меньше, то и ставить на перове место и запрос переписать примерно на такой:

Код:

SELECT * FROM (
  (SELECT * FROM `icons` WHERE `status` = "pending" AND `userId` IS NULL ORDER BY `id` ASC LIMIT 1)
  UNION
  (SELECT * FROM `icons` WHERE `status` = "pending" AND `userId`=1 ORDER BY `id` ASC LIMIT 1)
) AS t
ORDER BY `id` ASC
LIMIT 1

zerkms · 15 Янв 2011

Gas
id как PK так и так добавится в конец индекса в innodb

Только что закончился - выполнялся 255 секунд и это всё сортировка

не верю. optimize + analyze и повторить тест.
ps: даже 0.3с для такого запроса это передоз. какие индексы в таблице есть?

zerkms · 15 Янв 2011

нужно прикидывать по чём селективность меньше

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

zerkms · 15 Янв 2011

Chusha
1. http://phpclub.ru/talk/threads/Не-срабатывает-индекс-при-сортировке.66670/#post-593788
2. OR не всегда отсекает возможность использования индексов, ибо index merge

zerkms · 15 Янв 2011

Chusha
Дамп можешь дать?

zerkms · 15 Янв 2011

хотя - сейчас сам нагенерю.

zerkms · 15 Янв 2011

Всё, собственно, как и ожидалось:

PHP:

mysql> SELECT SQL_NO_CACHE * FROM tst WHERE status = 'deleted' ORDER BY id DESC LIMIT 1;
+--------+---------+
| id     | status  |
+--------+---------+
| 735388 | deleted |
+--------+---------+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE * FROM tst WHERE status = 'approved' ORDER BY id DESC LIMIT 1;
+--------+----------+
| id     | status   |
+--------+----------+
| 375388 | approved |
+--------+----------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM tst WHERE status = 'deleted' ORDER BY id DESC LIMIT 1;
+----+-------------+-------+------+---------------+--------+---------+-------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows   | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | tst   | ref  | status        | status | 22      | const | 228144 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM tst WHERE status = 'approved' ORDER BY id DESC LIMIT 1;
+----+-------------+-------+------+---------------+--------+---------+-------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows   | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | tst   | ref  | status        | status | 22      | const | 548170 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM tst;
+----------+
| COUNT(*) |
+----------+
|  1350488 |
+----------+
1 row in set (0.67 sec)

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

UPD
Догенерил до 2М, с рандомным распределением. Результат такой же (что логично).

zerkms · 15 Янв 2011

Chusha

PHP:

CREATE TABLE test.tst(
  id INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
  status VARCHAR (20) NOT NULL,
  PRIMARY KEY (id),
  INDEX status USING BTREE (status)
)
ENGINE = INNODB
AUTO_INCREMENT = 1837427
AVG_ROW_LENGTH = 33
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

zerkms · 15 Янв 2011

PHP:

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM icons WHERE status = 'approved' AND userid = 1 ORDER BY id LIMIT 1;
+----+-------------+-------+------+-------------------+------+---------+-------------+-------+-------------+
| id | select_type | table | type | possible_keys     | key  | key_len | ref         | rows  | Extra       |
+----+-------------+-------+------+-------------------+------+---------+-------------+-------+-------------+
|  1 | SIMPLE      | icons | ref  | SUI,status,userId | SUI  | 7       | const,const | 50466 | Using where |
+----+-------------+-------+------+-------------------+------+---------+-------------+-------+-------------+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE * FROM icons WHERE status = 'approved' AND userid = 1 ORDER BY id LIMIT 1;
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
| id  | date                | ssId | imported | userId | status   | dateProcessed | title | previewUrl | tags | json |
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
| 969 | 0000-00-00 00:00:00 | NULL |        0 |      1 | approved | NULL          |       |            |      |      |
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE * FROM icons WHERE status = 'deleted' AND userid = 1 ORDER BY id DESC LIMIT 1;
+--------+---------------------+------+----------+--------+---------+---------------+-------+------------+------+------+
| id     | date                | ssId | imported | userId | status  | dateProcessed | title | previewUrl | tags | json |
+--------+---------------------+------+----------+--------+---------+---------------+-------+------------+------+------+
| 616547 | 0000-00-00 00:00:00 | NULL |        0 |      1 | deleted | NULL          |       |            |      |      |
+--------+---------------------+------+----------+--------+---------+---------------+-------+------------+------+------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM icons WHERE status = 'deleted' AND userid = 1 ORDER BY id DESC LIMIT 1;
+----+-------------+-------+------+-------------------+------+---------+-------------+-------+-------------+
| id | select_type | table | type | possible_keys     | key  | key_len | ref         | rows  | Extra       |
+----+-------------+-------+------+-------------------+------+---------+-------------+-------+-------------+
|  1 | SIMPLE      | icons | ref  | SUI,status,userId | SUI  | 7       | const,const | 43446 | Using where |
+----+-------------+-------+------+-------------------+------+---------+-------------+-------+-------------+
1 row in set (0.00 sec)

Данных - 500к. 2 ночи, лень ждать пока нагенерится больше

zerkms · 15 Янв 2011

PHP:

mysql> SELECT SQL_NO_CACHE * FROM icons WHERE status = 'approved' ORDER BY id DESC LIMIT 1;
+--------+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
| id     | date                | ssId | imported | userId | status   | dateProcessed | title | previewUrl | tags | json |
+--------+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
| 616552 | 0000-00-00 00:00:00 | NULL |        0 |      1 | approved | NULL          |       |            |      |      |
+--------+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM icons WHERE status = 'approved' ORDER BY id DESC LIMIT 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | icons | index | SUI,status    | PRIMARY | 4       | NULL |    4 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Это специально?

Это не специально - я не вижу причин, с чего бы результат изменился. Собственно он и не изменился.

zerkms · 15 Янв 2011

5.1.51

У нас данные разные - потому и план он строит по-разному.

Вот результаты после optimize + analyze:

PHP:

mysql> SELECT SQL_NO_CACHE * FROM icons WHERE status = 'approved' ORDER BY id LIMIT 1;
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
| id  | date                | ssId | imported | userId | status   | dateProcessed | title | previewUrl | tags | json |
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
| 939 | 0000-00-00 00:00:00 | NULL |        0 |      5 | approved | NULL          |       |            |      |      |
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM icons WHERE status = 'approved' ORDER BY id LIMIT 1;
+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows   | Extra       |
+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+
|  1 | SIMPLE      | icons | ref  | SUI,status    | status | 2       | const | 250590 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

zerkms · 15 Янв 2011

Chusha
Даже не представляю, каким боком у тебя на запрос WHERE status = 'approved' ORDER BY id mysql взял и выбрал SUI а не status (как у меня в последнем посте)

zerkms · 15 Янв 2011

PHP:

mysql> show status like 'innodb_rows_read%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| Innodb_rows_read | 41378311 |
+------------------+----------+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE * FROM icons WHERE status = 'approved' ORDER BY id LIMIT 1;
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
| id  | date                | ssId | imported | userId | status   | dateProcessed | title | previewUrl | tags | json |
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
| 939 | 0000-00-00 00:00:00 | NULL |        0 |      5 | approved | NULL          |       |            |      |      |
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
1 row in set (0.00 sec)

mysql> show status like 'innodb_rows_read%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| Innodb_rows_read | 41378312 |
+------------------+----------+
1 row in set (0.00 sec)

Чтение тут одно, и это правильно, потому что по индексу status (который на самом деле дополнен PK, потому что innodb) + LIMIT 1 мы сразу находим искомую строку и выгребаем её

Вот с десяткой, чтобы было нагляднее:

PHP:

mysql> show status like 'innodb_rows_read%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| Innodb_rows_read | 41378322 |
+------------------+----------+
1 row in set (0.01 sec)

mysql> SELECT SQL_NO_CACHE * FROM icons WHERE status = 'approved' ORDER BY id LIMIT 10;
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
| id  | date                | ssId | imported | userId | status   | dateProcessed | title | previewUrl | tags | json |
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
| 939 | 0000-00-00 00:00:00 | NULL |        0 |      5 | approved | NULL          |       |            |      |      |
| 940 | 0000-00-00 00:00:00 | NULL |        0 |      4 | approved | NULL          |       |            |      |      |
| 941 | 0000-00-00 00:00:00 | NULL |        0 |      9 | approved | NULL          |       |            |      |      |
| 943 | 0000-00-00 00:00:00 | NULL |        0 |      2 | approved | NULL          |       |            |      |      |
| 944 | 0000-00-00 00:00:00 | NULL |        0 |      3 | approved | NULL          |       |            |      |      |
| 946 | 0000-00-00 00:00:00 | NULL |        0 |     10 | approved | NULL          |       |            |      |      |
| 948 | 0000-00-00 00:00:00 | NULL |        0 |      6 | approved | NULL          |       |            |      |      |
| 950 | 0000-00-00 00:00:00 | NULL |        0 |      3 | approved | NULL          |       |            |      |      |
| 952 | 0000-00-00 00:00:00 | NULL |        0 |      8 | approved | NULL          |       |            |      |      |
| 953 | 0000-00-00 00:00:00 | NULL |        0 |      2 | approved | NULL          |       |            |      |      |
+-----+---------------------+------+----------+--------+----------+---------------+-------+------------+------+------+
10 rows in set (0.00 sec)

mysql> show status like 'innodb_rows_read%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| Innodb_rows_read | 41378332 |
+------------------+----------+
1 row in set (0.00 sec)

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

Новичок

Новичок

Новичок

Продвинутый новичок

Продвинутый новичок

TDD infected

TDD infected

может по одной?

TDD infected

TDD infected

TDD infected

TDD infected

TDD infected

TDD infected

TDD infected

TDD infected

TDD infected

TDD infected

TDD infected

TDD infected