MySQL or conditions can be indexed to avoid full tables

In some cases, the or condition can avoid full table scan.

1. If the where statement contains the or condition, the myisam table can use indexes, but innodb does not.

1) MyISAM table:
Create table if not exists 'A '(
'Id' int (1) not null AUTO_INCREMENT,
'Uid' int (11) not null,
'Anum' char (20) default null,
Primary key ('id '),
KEY 'uid' ('uid ')
) ENGINE = MyISAM default charset = latin1 AUTO_INCREMENT = 6;

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)

2) innodb table:

Create table if not exists 'A '(
'Id' int (1) not null AUTO_INCREMENT,
'Uid' int (11) not null,
'Anum' char (20) default null,
Primary key ('id '),
KEY 'uid' ('uid ')
) ENGINE = InnoDB default charset = latin1 AUTO_INCREMENT = 6;

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 , region  from location where region = "melbourne" 

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.

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 );