Shulou(Shulou.com)06/01 Report--

mysql> select * from t_test;

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

| deptno | dname | loc |

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

| 10 | Research | Beijing |

| 20 | Maintenance | Huludao |

| 30 | Market | Tianjin |

| 40 | Leader | Qingdao |

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

4 rows in set (0.00 sec)

mysql> create index idx_test_dname on t_test(dname);

Query OK, 0 rows affected (0.31 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show keys from t_test\G

*************************** 1. row ***************************

Table: t_test

Non_unique: 1

Key_name: idx_test_dname

Seq_in_index: 1

Column_name: dname

Collation: A

Cardinality: 4

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.00 sec)

%Lea% index scan not used

mysql> explain select * from t_test where dname like '%Lea%';

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | t_test | ALL | NULL | NULL | NULL | NULL | 4 | Using where |

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

1 row in set (0.00 sec)

Lea% Use index scan

mysql> explain select * from t_test where dname like 'Lea%';

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | t_test | range | idx_test_dname | idx_test_dname | 47 | NULL | 1 | Using where |

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

1 row in set (0.12 sec)

mysql> explain select dname from t_test where dname like 'Lea%';

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | t_test | index | idx_test_dname | idx_test_dname | 47 | NULL | 4 | Using where; Using index |

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

1 row in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.