Меню
Что нового?

Explain --> possible_keys --> rows

  • Автор темы Автор темы SeaGull
  • Дата начала Дата начала

SeaGull

Junior Member
Explain --> possible_keys --> rows

День добрый.

MySQL Server version: 5.0.24a
Занялся экспериментами с оптимизацией баз на сайте и наткнулся на совершенно непонятные мне результаты от EXPLAIN.

Имеется Гостевая книга:
Код: 
CREATE TABLE `GuestBook` (
  `id` mediumint(8) unsigned NOT NULL default '0',
  `login` char(20) NOT NULL default '',
  `text` text NOT NULL,
  `date` datetime default NULL,
  `moderated` char(50) NOT NULL default ''
) ENGINE=MyISAM
В поле "moderated" хранится информация о том, кто и когда удалил сообщение.
Соответсвенно, пользователям видны только "не удаленные" сообщения, т.е. те, для которых поле "moderated" пустое.

select count(*) from GuestBook;
167745

select count(*) from GuestBook where moderated = '';
163467

select count(*) from GuestBook where moderated != '';
4278

explain select count(*) from GuestBook where moderated='';
Код: 
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | GuestBook | ALL  | NULL          | NULL | NULL    | NULL | 167745 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
Далее завожу индекс и начинается "странное"...

alter table GuestBook add INDEX (moderated);
Query OK, 167745 rows affected (17.80 sec)

explain select count(*) from GuestBook where moderated='';
Код: 
+----+-------------+-----------+------+---------------+-----------+---------+-------+--------+--------------------------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows   | Extra                    |
+----+-------------+-----------+------+---------------+-----------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | GuestBook | ref  | moderated     | moderated | 50      | const | 144389 | Using where; Using index |
+----+-------------+-----------+------+---------------+-----------+---------+-------+--------+--------------------------+
alter table GuestBook drop INDEX moderated;
Query OK, 167745 rows affected (8.38 sec)

alter table GuestBook add INDEX (moderated(1));
Query OK, 167745 rows affected (8.40 sec)

explain select count(*) from GuestBook where moderated='';
Код: 
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | GuestBook | ALL  | moderated     | NULL | NULL    | NULL | 125809 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
Кто разбирается, подскажите пожалуйста:
1. Почему во 2-ом EXPLAIN поле rows = 144389, а не 163467 (см. 2-ой SELECT) ?
2. Почему в 3-ем EXPLAIN не используется индекс ?
3. Почему в 3-ем EXPLAIN поле rows меньше общего числа записей базы, хотя индекс не используется ?
4. Какой из результатов, согласно EXPLAIN, следует считать лучше оптимизированным ?
 
SeaGull
1. Почему во 2-ом EXPLAIN поле rows = 144389, а не 163467 (см. 2-ой SELECT) ?
Нажмите для раскрытия...
Потому что эксплайн выдает приблизительное количество рядов которые необходимо просмотреть для поиска нужных записей.

`moderated` char(50) NOT NULL default ''
Нажмите для раскрытия...
Что хранится в этом поле? Если это поле флаг - отмодерировано/нет, то его лучше сделать числового типа и хранить 0 если нет и 1 если да. Если это поле имеет еще какой-то смысл, то нужно добавить еще одно числовое поле-флаг, так как это будет и логичнее и по идее должно быть быстрее(даже с индексами).
 
Mr_Max
OPTIMIZE TABLE ?
Нажмите для раскрытия...
Да проводил. Ничего не изменилось.

phprus
Потому что эксплайн выдает приблизительное количество рядов которые необходимо просмотреть для поиска нужных записей.
Нажмите для раскрытия...
Спасибо. Еще раз посмотрел документацию, действительно есть приписка, что "столбец rows в выводе оператора EXPLAIN - опытное предположение оптимизатора связей MySQL.". Теперь буду знать, что это означает :)

Что хранится в этом поле? Если это поле флаг - отмодерировано/нет, то его лучше сделать числового типа и хранить 0 если нет и 1 если да.
Нажмите для раскрытия...
Да, конечно. Но это просто тестовая база, дабы разобраться с тонкостями индексов.

А по вопросам 2-4 ничего не понятно ?
 
2. Почему в 3-ем EXPLAIN не используется индекс ?
Нажмите для раскрытия...
На счет этого у меня есть предположение, что по индексу длинной в один символ по какой-то причине невозможно проверить есть ли в этом поле строка нулевой длинны или там еще чтото есть и по этому MySQL его не использует. Хотя это только предположение.
 
phprus
На счет этого у меня есть предположение, что по индексу длинной в один символ по какой-то причине невозможно проверить есть ли в этом поле строка нулевой длинны или там еще чтото есть и по этому MySQL его не использует.
Нажмите для раскрытия...
Нет, не то. Проверил:

alter table GuestBook drop INDEX moderated;
update GuestBook set moderated='TEST' where moderated='';
alter table GuestBook add INDEX (moderated(25));
explain select count(*) from GuestBook where moderated='TEST';
Код: 
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | GuestBook | ALL  | moderated     | NULL | NULL    | NULL | 125809 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
 
select count(*) from GuestBook;
167745

select count(*) from GuestBook where moderated = '';
163467
Нажмите для раскрытия...
Чисто между делом, для второго запроса индекс использовать не имеет смысла с точки зрения производительности...
Полный скан будет быстрее.
Правда, не уверен, что оптимизатору MySQL это известно :)
 
SeaGull
у тебя достаточно частный случай - низкая(ий) cardinality.
How to avoid table scan

[SQL]explain select count(*) from GuestBook where moderated='TEST' LIMIT 1;[/SQL]
Пожалуйста, explain лучше, а скорость запроса хуже.
 
Войдите или зарегистрируйтесь для ответа.
Сверху