First of all, MySQL itself does not support full-text search. Using fuzzy query will lead to full table scan and query efficiency is low.

Secondly, MySQL does not support complex conditional queries well. At most, MySQL uses the index involved in one condition to filter, and then the remaining conditions can only be filtered in memory during row traversal.

MySQL complex WHERE condition analysis

Suppose you have an order table as follows, where ID is the primary key, iID is the unique index, user_id is the normal index, and price has no index.

orders(id, order_id, user_id, price)

Based on the above table, analyze the following SQL statements with Where conditions.

update orders set price = price * 0.8 where user_id = 102 and order_id > 'N0001' and order_id < 'N0012';
Copy the code

The Where condition in the SQL statement above uses two indexes, order_id and user_id. MySQL selects an Index based on indexes such as Index selectivity, and the other Where condition that is not used is used as a common Filter condition. The used Index is called an Index Key, and the common Filter condition is called a Table Filter.

Therefore, the execution process of this SQL is to read the Index records in the Index Key range successively, then read the complete data records back to the Table, and then return to the MySQL service layer for filtering according to the Table Filter.

Index Filter can reduce the number of table returns and the number of records returned to the MySQL service layer, reduce the interaction overhead between the storage engine and the service layer, and improve the efficiency of SQL execution.

Because only one index can be used to filter complex query conditions, a large number of I/O operations are required to read row data and CPU is consumed for memory filtering, resulting in query performance degradation.

reference

Why is ElasticSearch better for full-text indexing than MySQL

MySQL complex WHERE condition analysis