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

Perfilev

Новичок
Ситуация такая: есть табличка с 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

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

Вурдалак

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

Вурдалак

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

zerkms

TDD infected
Команда форума
. Так вот, подсознание подсказало правильное решение для второго случая - force index(primary)
Какой бред. Если мускул решил, что совпадений слишком много - значит их слишком много. Значит фуллскан будет выполнить быстрее, чем миллиард сиков по датафайлу.
 

Gas

может по одной?
Я бы пробовал индекс (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

TDD infected
Команда форума
Gas
id как PK так и так добавится в конец индекса в innodb

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

zerkms

TDD infected
Команда форума
нужно прикидывать по чём селективность меньше
селективность каждого из полей в составном индексе (при условии, что будут использоваться все части) не имеет значения.
 

zerkms

TDD infected
Команда форума
Всё, собственно, как и ожидалось:

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

TDD infected
Команда форума
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

TDD infected
Команда форума
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

TDD infected
Команда форума
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

TDD infected
Команда форума
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

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

zerkms

TDD infected
Команда форума
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)
 
Сверху