MySQL explain Command

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)

MySQL Analyze Command

You can analyze a table to update its statistics as follows:

 

--------------

create table andrews_table like

information_schema.PROCESSLIST

--------------

 

Query OK, 0 rows affected (0.01 sec)

 

--------------

insert into andrews_table select * from

information_schema.PROCESSLIST

--------------

 

Query OK, 12 rows affected (0.00 sec)

Records: 12  Duplicates: 0  Warnings: 0

 

--------------

analyze table andrews_table

--------------

 

+--------------------+---------+----------+----------+

| Table              | Op      | Msg_type | Msg_text |

+--------------------+---------+----------+----------+

| test.andrews_table | analyze | status   | OK       |

+--------------------+---------+----------+----------+

1 row in set (0.00 sec)

MySQL create table ... like ...

If you want to create a table with the same description as another table, you can do this using like. In the example below, I create a table called andrews_table with 1 row of data. Then I use like to create a table called another_table with the same description. This table is empty to start with. To copy the data from andrews_table into another_table, I use a simple insert statement:

 

mysql> create table andrews_table

    -> (col1 varchar(10));

Query OK, 0 rows affected (0.03 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.12 sec)

 

mysql> create table another_table

    -> like andrews_table;

Query OK, 0 rows affected (0.00 sec)

 

mysql> desc another_table;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| col1  | varchar(10) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

1 row in set (0.06 sec)

 

mysql> select * from another_table;

Empty set (0.00 sec)

 

mysql> insert into another_table

    -> (select * from andrews_table);

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> select * from another_table;

+------+

| col1 |

+------+

| Fred |

+------+

1 row in set (0.00 sec)

 

mysql>