In this example, a table is created with a TIME column. It has 12 rows.
A select statement shows that the table has 3 rows with TIME > 1000. The explain command is then used to see how this statement works. The rows column in the output from the explain
command is MySQL’s estimate of the number of rows which the query will
need to examine. In the absence of an index, My SQL needs to do a full
table scan so the answer is 12.
An index is then added to the table’s TIME column and the explain command is rerun. It uses the index and estimates that 4 rows will need to be examined to run the query:
--------------
create table t1
like information_schema.PROCESSLIST
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
insert into t1 select * from
information_schema.PROCESSLIST
--------------
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
--------------
select count(*) from t1 where TIME > 1000
--------------
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
--------------
explain
select count(*) from t1 where TIME > 1000
--------------
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 12 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
--------------
create index i on t1(TIME)
--------------
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
--------------
explain
select count(*) from t1 where TIME > 1000
--------------
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | range | i | i | 4 | NULL | 4 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
2 comments:
These facts are really interesting. Few of them were well known for me but many of them were brand new for me too!
I will print this one out and show to my friends because they will be definitely interested in that. Thanks!
MySQL
Good for who is focus on MySql performance tuning. Why don't you do explain other sections in EXPLAIN command.That also must helpful.Thank you
Post a Comment