Hello,
I have the following kind of table :
CREATE TABLE route (
route_k BIGINT UNSIGNED NOT NULL,
state ENUM('down', 'up') NOT NULL,
[ ... other columns ...]
PRIMARY KEY (route_k),
KEY state (state)
) TYPE = MyISAM;
---------------------------
Now here is the time taken by two semantically identical requests :
mysql> select avg(route_k) from route where state='down';
+--------------------------+
| avg(route_k) |
+--------------------------+
| 9269165327512991744.0000 |
+--------------------------+
1 row in set (0.08 sec)
mysql> select avg(route_k) from route where state!='up';
+--------------------------+
| avg(route_k) |
+--------------------------+
| 9269165327512991744.0000 |
+--------------------------+
1 row in set (0.21 sec)
EXPLAIN :
mysql> explain select avg(route_k) from route where state='down';
+-------+------+---------------+-------+---------+-------+-------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+-------+---------+-------+-------+------------+
| route | ref | state | state | 1 | const | 17479 | where used |
+-------+------+---------------+-------+---------+-------+-------+------------+
1 row in set (0.00 sec)
mysql> explain select avg(route_k) from route where state!='up';
+-------+------+---------------+------+---------+------+--------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+--------+------------+
| route | ALL | NULL | NULL | NULL | NULL | 141883 | where used |
+-------+------+---------------+------+---------+------+--------+------------+
1 row in set (0.00 sec)
With "state != 'up'", it fails to understand that it can use the index
to do the job and get better performance.
---------------------------
And now two other requests (complementary in fact) :
mysql> select avg(route_k) from route where state='up';
+--------------------------+
| avg(route_k) |
+--------------------------+
| 9230562543838910464.0000 |
+--------------------------+
1 row in set (0.54 sec)
mysql> select avg(route_k) from route where state!='down';
+--------------------------+
| avg(route_k) |
+--------------------------+
| 9230562543838910464.0000 |
+--------------------------+
1 row in set (0.22 sec)
EXPLAIN :
mysql> explain select avg(route_k) from route where state='up';
+-------+------+---------------+-------+---------+-------+--------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+-------+---------+-------+--------+------------+
| route | ref | state | state | 1 | const | 124402 | where used |
+-------+------+---------------+-------+---------+-------+--------+------------+
1 row in set (0.00 sec)
mysql> explain select avg(route_k) from route where state!='down';
+-------+------+---------------+------+---------+------+--------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+--------+------------+
| route | ALL | NULL | NULL | NULL | NULL | 141883 | where used |
+-------+------+---------------+------+---------+------+--------+------------+
1 row in set (0.00 sec)
Here it's another problem : with "state='up'" it fails to recognize that
'up' represents roughly 80% of all rows, and thus a scan of the table should
be used instead of the index (the documentation states the index is used only
if the estimated number of selected rows is below one third of the total).
Regards
Antoine.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
