Index and like Optimization

Unindexed

Mysql> alter table modulestatus drop index IMEI;
Query OK, 457922 rows affected (4.29 Sec)
Records: 457922 duplicates: 0 Warnings: 0

Mysql> select count (*) from modulestatus where IMEI like '1% ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 270 |
+ ---------- +
1 row in SET (0.53 Sec)

Mysql> select count (*) from modulestatus where IMEI like '% 1 ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 35499 |
+ ---------- +
1 row in SET (0.66 Sec)

Mysql> select count (*) from modulestatus where IMEI like '1% 'and '9% ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 270 |
+ ---------- +
1 row in set, 1 warning (0.49 Sec)

 

Create an index

Mysql> alter table modulestatus add index (IMEI );
Query OK, 457922 rows affected (31.67 Sec)
Records: 457922 duplicates: 0 Warnings: 0

Mysql> select count (*) from modulestatus where IMEI like '1% ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 270 |
+ ---------- +
1 row in SET (0.04 Sec)

Mysql> select count (*) from modulestatus where IMEI like '% 1 ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 35499 |
+ ---------- +
1 row in SET (0.80 Sec)

Mysql> select count (*) from modulestatus where IMEI like '1% 'and '9% ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 270 |
+ ---------- +
1 row in set, 1 warning (0.00 Sec)

 

Conclusion

1. Like % Keyword: Index failure. Full table scan is used. However, you can use the flip Function + like prefix fuzzy query + build the index of the flip function = take the flip function index instead of the full table scan.

2. Like keyword %: The index is valid.

3. The like % keyword % index is invalid and the reverse index cannot be used.

 

Unindexed

 

Mysql> select count (*) from modulestatus where IMEI like '% 100 ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 326019 |
+ ---------- +
1 row in SET (0.73 Sec)

 

Mysql> select count (*) from modulestatus where locate ('1', IMEI );
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 326019 |
+ ---------- +
1 row in SET (0.76 Sec)

 

Create an index

Mysql> alter table modulestatus add index (IMEI );
Query OK, 457922 rows affected (11.06 Sec)
Records: 457922 duplicates: 0 Warnings: 0

Mysql> select count (*) from modulestatus where IMEI like '% 100 ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 326019 |
+ ---------- +
1 row in SET (0.93 Sec)

Mysql> select count (*) from modulestatus where locate ('1', IMEI );
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 326019 |
+ ---------- +
1 row in SET (0.95 Sec)

 

 

Conclusion

Locate (Str, Colum) function, can replace column like '% STR %', but there is no obvious difference in efficiency.

After an index is created, the like '% STR %' and locate will be slowed down.(Str, Colum)Function query speed.

 

No index created

 

Mysql> select count (*) from modulestatus where IMEI like '% 100 ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 326019 |
+ ---------- +
1 row in SET (0.73 Sec)

 

Mysql> select count (*) from modulestatus where instr (IMEI, '1 ');
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 326019 |
+ ---------- +
1 row in SET (0.79 Sec)

 

 

Create an index

 

Mysql> select count (*) from modulestatus where IMEI like '% 100 ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 326019 |
+ ---------- +
1 row in SET (1.00 Sec)

 

Mysql> select count (*) from modulestatus where instr (IMEI, '1 ');
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 326019 |
+ ---------- +
1 row in SET (0.84 Sec)

 

Conclusion

The instr (Colum, STR) function can replace column like '% STR %', but the efficiency is not significantly different.

After an index is created, the like '% STR %' andInstr (Colum, STR)Function query speed

 

No index created

Mysql> select count (*) from modulestatus where IMEI like '% 1 ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 35499 |
+ ---------- +
1 row in SET (0.90 Sec)

Mysql> select count (*) from modulestatus where reverse (IMEI) like reverse ('% 1 ');

+ ---------- +
| Count (*) |
+ ---------- +
| 1, 35499 |
+ ---------- +
1 row in SET (0.70 Sec)

 

Create an index

Mysql> alter table modulestatus add index (IMEI );
Query OK, 457922 rows affected (10.04 Sec)
Records: 457922 duplicates: 0 Warnings: 0

Mysql> select count (*) from modulestatus where IMEI like '% 1 ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 35499 |
+ ---------- +
1 row in SET (1.06 Sec)

Mysql> select count (*) from modulestatus where reverse (IMEI) like reverse ('% 1 ')

+ ---------- +
| Count (*) |
+ ---------- +
| 1, 35499 |
+ ---------- +
1 row in SET (0.84 Sec)

 

Conclusion

1. Creating an index reduces the query speed of like '% str' and reverse (column) like reverse (' % str.

2. Whether an index is created or not, reverse (column) like reverse ('% STR') is faster than column like '% STR.

Note: When executing column like '% str', the execution plan shows that the consumption value, I/O value, and CPU value are not very large, because the fuzzy query in front of like leads to index failure, scan the entire table.

Use the flip Function + prefix like fuzzy query + create a flip Function Index = take the flip Function Index without a full scan. Effectively reduce the consumption value, Io value, CPU value, especially the IO value reduction.

 

Index and like Optimization