SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 30.01.2011 02:52:06

rs7
Участник
Зарегистрирован: 30.01.2011
Сообщений: 4

ORDER BY и USING WHERE

Здравствуйте.
Есть вот такая табличка:

mysql> DESCRIBE `friends_history`;
+------------+----------------------+------+-----+-------------------+-----------------------------+
| Field      | Type                 | Null | Key | Default           | Extra                       |
+------------+----------------------+------+-----+-------------------+-----------------------------+
| _id        | int(11) unsigned     | NO   | PRI | NULL              | auto_increment              |
| user_id    | int(11) unsigned     | NO   | PRI | NULL              |                             |
| type       | enum('remove','add') | NO   |     | NULL              |                             |
| users_list | mediumtext           | NO   |     | NULL              |                             |
| date       | timestamp            | NO   | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+----------------------+------+-----+-------------------+-----------------------------+

mysql> SHOW INDEXES FROM `friends_history`;
+-----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| friends_history |          0 | PRIMARY      |            1 | _id         | A         |    49470030 |     NULL | NULL   |      | BTREE      |         |               |
| friends_history |          0 | PRIMARY      |            2 | user_id     | A         |    49470030 |     NULL | NULL   |      | BTREE      |         |               |
| friends_history |          1 | date         |            1 | date        | A         |    49470030 |     NULL | NULL   |      | BTREE      |         |               |
| friends_history |          1 | user_id+date |            1 | user_id     | A         |     2748335 |     NULL | NULL   |      | BTREE      |         |               |
| friends_history |          1 | user_id+date |            2 | date        | A         |    49470030 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

И вот такой к ней запрос:

mysql> EXPLAIN SELECT `type`,`users_list` FROM `friends_history` WHERE `user_id` = 1052662 ORDER BY `date` DESC;
+----+-------------+-----------------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table           | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-----------------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | friends_history | ref  | user_id+date  | user_id+date | 4       | const |   33 | Using where |
+----+-------------+-----------------+------+---------------+--------------+---------+-------+------+-------------+

Объясните, пожалуйста, из-за чего здесь USING WHERE? Можно ли его побороть?

Второй вопрос состоит в том, что запрос без сортировки исправно выдаёт отсортированный по date результат (date в нашем случае - дата добавления записи). Можно ли полагаться на это? Ведь по идеи mysql не обязан этого делать.

mysql> EXPLAIN SELECT `type`,`users_list` FROM `friends_history` WHERE `user_id` = 1052662;
+----+-------------+-----------------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table           | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+-----------------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | friends_history | ref  | user_id+date  | user_id+date | 4       | const |   33 |       |
+----+-------------+-----------------+------+---------------+--------------+---------+-------+------+-------+

Спасибо.)

Неактивен

 

#2 30.01.2011 12:01:11

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: ORDER BY и USING WHERE

На колонке, которая поддерживает NULL, — нельзя. NULLы то отсеивать надо
как-то wink

Полагаться не стоит, так как это не документированная особенность MyISAM.
Например, удаление строки, а потом добавление новой, вставит строку физи-
чески не в конец таблицы, а в освободившееся место, и недокументированная
сортировка нарушится. Ну и если будете переходить на InnoDB, сможете это
сделать куда проще, если не будете использовать такие особенности smile

Неактивен

 

#3 30.01.2011 12:24:02

rs7
Участник
Зарегистрирован: 30.01.2011
Сообщений: 4

Re: ORDER BY и USING WHERE

Эм...)

Так никакая из колонок NULL и не поддерживает.
И это уже InnoDB.

mysql-5.5.8

Может так понятнее


CREATE TABLE `friends_history` (
  `_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `type` enum('remove','add') NOT NULL COMMENT '1-add; 0-remove;',
  `users_list` mediumtext NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`_id`,`user_id`),
  KEY `date` (`date`),
  KEY `user_id+date` (`user_id`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=47224987 DEFAULT CHARSET=utf8
 

Отредактированно rs7 (30.01.2011 12:29:48)

Неактивен

 

#4 31.01.2011 02:12:38

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: ORDER BY и USING WHERE

Да, так куда понятнее, а то смотрел на DEFAULT NULL wink

В InnoDB строки сортируются по PRIMARY KEY всегда, на это можно полагаться.
Соответственно, если _id у Вас растет честным автоинкрементом, то он будет
расти с датой в том числе smile Кстати, а зачем там user_id в PK?

Что касается Using where в данном случае — смотрите последний комментарий
Тобиаса в http://bugs.mysql.com/bug.php?id=30733 . Впрочем, насколько я пони-
маю, это проблема отображения, а не выполнения запроса.

Неактивен

 

#5 31.01.2011 03:13:16

rs7
Участник
Зарегистрирован: 30.01.2011
Сообщений: 4

Re: ORDER BY и USING WHERE

paulus написал:

В InnoDB строки сортируются по PRIMARY KEY всегда, на это можно полагаться.
Соответственно, если _id у Вас растет честным автоинкрементом, то он будет
расти с датой в том числе smile

Кстати, а зачем там user_id в PK?

Что касается Using where в данном случае — смотрите последний комментарий
Тобиаса в http://bugs.mysql.com/bug.php?id=30733 . Впрочем, насколько я пони-
маю, это проблема отображения, а не выполнения запроса.

Спасибо. Автоинкремент у меня не совсем честный - стоит innodb_autoinc_lock_mode = 2. Но учитывая специфику приложения - полагаться можно. tongue

Добавлено секционирование по этому полю. roll

Почитал. Интересно, что ситуация у человека наоборот: у него при использование префикса многостолбцового индекса возникает USING WHERE, у меня же - только если включить сортировку по второй части индекса ORDER BY `date`. Причём, появляется не USING FILESORT, как если бы эта самая, вторая часть, не подхватывалась, а USING WHERE - типа индекс вообще не используется. Но, при этот, тот же самый EXPLAIN сообщает обратное. hmm

Если, как Вы считаете, это проблема отображения, то как это правильно проверить? Выполнить запрос несколько раз пока не устаканется время выполнения и последний результат считать правильным? SQL_NO_CACHE, как я понимаю, спасает только от кэша запросов, а более глубокие процессы кэширования принудительно выключить нельзя. Потому как время выполнения запроса в первые разы больше чем в следующие.

Отредактированно rs7 (31.01.2011 04:59:52)

Неактивен

 

#6 31.01.2011 19:02:24

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: ORDER BY и USING WHERE

USING WHERE — не означает, что индекс не используется. Это означает,
что результат дополнительно проверяется на соблюдение условий. Наличие
индекса при вытаскивании данных можно увидеть в столбце key. Что касается
Тобиаса — у него ситуация такая же — используется левая часть индекса
(key_len=4, только по _id в Вашем случае), и отображается «Using where».

А что Вы называете более глубокими процессами кэширования? При первом
проходе Вы вытаскиваете с диска страницы индекса, которых еще там не
было. При втором проходе они там уже есть, поэтому на диск за ними идти
не нужно.

Неактивен

 

#7 01.02.2011 05:00:10

rs7
Участник
Зарегистрирован: 30.01.2011
Сообщений: 4

Re: ORDER BY и USING WHERE

Спасибо за ответы.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson