Explain --> possible_keys --> rows

[SeaGull]

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, следует считать лучше оптимизированным ?

 

[Mr_Max]

SeaGull
OPTIMIZE TABLE ?

 

[phprus]

SeaGull

1. Почему во 2-ом EXPLAIN поле rows = 144389, а не 163467 (см. 2-ой SELECT) ?

Потому что эксплайн выдает приблизительное количество рядов которые необходимо просмотреть для поиска нужных записей.

`moderated` char(50) NOT NULL default ''

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

 

[SeaGull]

Mr_Max
OPTIMIZE TABLE ?

Да проводил. Ничего не изменилось.

phprus
Потому что эксплайн выдает приблизительное количество рядов которые необходимо просмотреть для поиска нужных записей.

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

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

Да, конечно. Но это просто тестовая база, дабы разобраться с тонкостями индексов.

А по вопросам 2-4 ничего не понятно ?

 

[phprus]

2. Почему в 3-ем EXPLAIN не используется индекс ?

На счет этого у меня есть предположение, что по индексу длинной в один символ по какой-то причине невозможно проверить есть ли в этом поле строка нулевой длинны или там еще чтото есть и по этому MySQL его не использует. Хотя это только предположение.

 

[SeaGull]

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 |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

 

[Krishna]

select count(*) from GuestBook;
167745

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

Чисто между делом, для второго запроса индекс использовать не имеет смысла с точки зрения производительности...
Полный скан будет быстрее.
Правда, не уверен, что оптимизатору MySQL это известно

 

[Gas]

SeaGull
у тебя достаточно частный случай - низкая(ий) cardinality.
How to avoid table scan

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