How to use Union to optimize Like statement in MySQL

Use Union to optimize Like statements

1) Sometimes, you may need to use the or operator in the query for comparison. When the or keyword is used too frequently in the where clause, it may cause the MySQL optimizer to mistakenly choose a full table scan to retrieve records. The union clause can make queries execute faster, especially when one of the queries has an optimized index and the other query also has an optimized index.

For example, when there are indexes on first_name and last_name respectively, execute the following query statement:

mysql> select * from students where first_name like 'Ade%' or last_name like 'Ade%'

Compared with the above query and the following query that uses union to merge two query statements, Much slower.

mysql> select * from students where first_name like 'Ade%' union all select * from students wherelast_name like 'Ade%'

The above is the detailed content of How to use Union to optimize Like statement in MySQL. For more information, please follow other related articles on the PHP Chinese website!