About the or conditions and indexes in mysql
1) myisam table:
Mysql> explain select * from a where id = 1 or uid = 2;
+ ---- + ------------- + ------- + ------------- + --------------- + ------------- + --------- + ------ + --------------------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------------- + --------------- + ------------- + --------- + ------ + --------------------------------------- +
| 1 | SIMPLE | a | index_merge | PRIMARY, uid | PRIMARY, uid | 4, 4 | NULL | 2 | Using union (PRIMARY, uid); Using where |
+ ---- + ------------- + ------- + ------------- + --------------- + ------------- + --------- + ------ + --------------------------------------- +
1 row in set (0.00 sec)
In mysql5.0 and the updated version, the two single-column indexes are used for scanning at the same time, and then the results are merged. Index merge requires a large amount of cpu and memory resources in the cache, and sorting of algorithms.
2) innodb table: (alter table a engine = innodb)
Mysql> explain select * from a where id = 1 or uid = 2;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- +
| 1 | SIMPLE | a | ALL | PRIMARY, uid | NULL | 5 | Using where |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- +
1 row in set (0.00 sec)
2. all or conditions must be independent indexes:+ ------- + Response ----------------------------------------------------------------------------------------------------------------------
| Table | Create Table
+ ------- + Response ----------------------------------------------------------------------------------------------------------------------
| A | create table 'A '(
'Id' int (1) not null AUTO_INCREMENT,
'Uid' int (11) not null,
'Anum' char (20) default null,
Primary key ('id ')
) ENGINE = MyISAM AUTO_INCREMENT = 6 default charset = latin1 |
+ ------- + Response ----------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
To view the description, follow these steps:
Mysql> explain select * from a where id = 1 or uid = 2;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- +
| 1 | SIMPLE | a | ALL | PRIMARY | NULL | 5 | Using where |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- +
1 row in set (0.00 sec)
The entire table is scanned.
3. replace OR with UNION (applicable to index columns)In general, replacing OR in the WHERE clause with UNION will produce better results. using OR for the index column will cause a full table scan.
Note that the preceding rules are only valid for multiple index columns. if a column is not indexed, the query efficiency may be reduced because you have not selected OR.
In the following example, both LOC_ID and REGION have indexes.
Efficient:
select loc_id , loc_desc , region from location where loc_id = 10 union select loc_id , loc_desc , regionfrom location where region = "melbourne"and loc_id != 10
Inefficiency:
select loc_id , loc desc , region from location where loc_id = 10 or region = "melbourne"
If you insist on using OR, you need to write the index columns with the least records at the beginning.
Query the value distribution in the table (check the data base of each branch in the where condition) to determine which value is placed on the front edge. Select sum (loc_id = 10), sum (region = "melbourne ") from location/G ************************* 1. row ********************** SUM (loc_id = 10): 7992SUM (region = "melbourne "): 30
Check and find that you should put region = "melbourne" on the front side.
4. replace or with in. this is a simple and easy-to-remember rule, but the actual execution results must be tested. in oracle8i, the execution paths of the two seem to be the same.
Inefficiency:
Select .... From location where loc_id = 10 or loc_id = 20 or loc_id = 30
Efficient
Select... From location where loc_in in (10, 20, 30 );
