MySQL study--index Enforcement and neglect

MySQL study--index Enforcement and neglect

1. View table structure

Mysql> Show CREATE TABLE Emp\g

1. Row ***************************

table: empcreate table: create table  ' emp '   (   ' empno '  int (4)  NOT NULL DEFAULT  ' 0 ',   ' ename '   varchar  DEFAULT NULL,   ' JOB '  varchar (9)  DEFAULT NULL,   ' MGR '  int (4)  DEFAULT NULL,   ' HIRE '  date DEFAULT NULL,   ' SAL '  int (7)  DEFAULT NULL,   ' COMM '  int (7)  DEFAULT NULL,   ' Deptno '  int (2)  DEFAULT NULL,  PRIMARY KEY  (' empno '),  key  ' Deptno '   (' deptno '),  constraint  ' emp_ibfk_1 '  FOREIGN KEY  (' deptno ')   references  ' dept '   (' DeptNO '))  engine=innodb default charset=latin11 row in  set  (0.00 sec) 

---empno the primary key in this table and has an index on the Deptno

Mysql> select * from EMP;

+-------+--------+-----------+------+------------+------+------+--------+| empno | ename   | JOB       | MGR  | HIRE        | sal  | comm | deptno |+-------+--------+--------- --+------+------------+------+------+--------+|  7369 | smith  | clerk      | 7902 | 1980-12-17 |  800 | null |      20 | |   7499 | allen  | salesman  | 7698 | 1981-02-20  | 1600 |  300 |     30 | |   7521 | WARD   | SALESMAN  | 7698 |  1981-02-22 | 1250 |  500 |     30 | |   7566 |&nbSp jones  | manager   | 7839 | 1981-04-02 | 2975 |  null |     20 | |   7654 | martin | salesman  | 7698 | 1981-09-28 |  1250 | 1400 |     30 | |   7698 | BLAKE  | MANAGER   | 7839 |  1981-05-01 | 2850 | null |     30 | |   7782 | CLARK  | MANAGER   | 7839 |  1981-06-09 | 2450 | null |     10 | |   7788 | SCOTT  | ANALYST   | 7566 |  1987-07-13 | 3000 | null |     10 | |   7839 | king   | president&nbsP;| null | 1981-11-17 | 5000 | null |     10  | |   7844 | turner | salesman  | 7698 | 1981-09-08 |  1500 |    0 |     30 | |   7876 | adams  | clerk     | 7788 |  1987-06-13 | 1100 | null |     20 | |   7900 | james  | clerk     | 7698 |  1981-12-03 |  950 | null |     30 | |   7902 | FORD   | ANALYST   | 7566 |  1981-12-03 | 3000 | null |     20 | |   7934 | miller | clerk     | 7782 | 1982-01-23 | 1300 | null |      10 |+-------+--------+-----------+------+------------+------+------+--------+14 rows in  set  (0.00 SEC)

1) Full table scan query

Mysql> explain select * from EMP \g

1. Row ***************************

Id:1 select_type:simple table:emp Type:ALLpossible_keys:NULL key:null key_len:null Ref:null rows:14 extra:null1 Row in Set (0.00 sec)

2, through the primary key query

Mysql> explain select * from EMP where empno=7788\g

1. Row ***************************

Id:1 select_type:simple table:emp Type:constpossible_keys:PRIMARY key:primary Key_len: 4 Ref:const rows:1 extra:null1 row in Set (0.02 sec)

3) Ignore primary key index

Mysql> explain SELECT * from emp Ignore index (PRI) where empno=7788\g

1. Row ***************************

Id:1 select_type:simple table:emp Type:ALLpossible_keys:NULL key:null key_len:null Ref:null rows:14 extra:using where1 row in Set (0.00 sec)

4) forced use of primary key

Mysql> explain SELECT * FROM EMP Force index (PRI) where empno=7788\g

1. Row ***************************

Id:1 select_type:simple table:emp Type:constpossible_keys:PRIMARY key:primary Key_len: 4 Ref:const rows:1 extra:null1 row in Set (0.00 sec)

5) query by index Deptno

Mysql> explain select * from EMP where deptno=10\g

1. Row ***************************

Id:1 select_type:simple table:emp Type:refpossible_keys:deptno key:deptno Key_len:5 Ref:const rows:4 extra:null1 Row in Set (0.00 sec)

6) Ignore the use of the index

Mysql> explain SELECT * from EMP Ignore index (DEPTNO) where deptno=10\g

1. Row ***************************

Id:1 select_type:simple table:emp Type:ALLpossible_keys:NULL key:null key_len:null Ref:null rows:14 extra:using where1 row in Set (0.00 sec)

7) Force the use of the index

Mysql> explain SELECT * FROM EMP Force index (DEPTNO) where deptno=10\g

1. Row ***************************

Id:1 select_type:simple table:emp Type:refpossible_keys:deptno key:deptno Key_len:5 Ref:const rows:4 extra:null1 Row in Set (0.00 sec)

This article is from the "Tianya blog," Please make sure to keep this source http://tiany.blog.51cto.com/513694/1726065

MySQL study--index Enforcement and neglect