How Can I Make MySQL's FIND_IN_SET Function Utilize Indices?

Can MySQL FIND_IN_SET be Made to Use Indices?

MySQL's FIND_IN_SET function is notorious for not utilizing indices, resulting in inefficient queries. However, there are alternative approaches to achieve the desired indexing behavior when dealing with comma-separated lists.

The Range Approach

The technique described in the provided answer suggests using a prepared statement with a parameter accepting a string in the form of a comma-separated list. By using the range type with a non-NULL key in the prepared statement, it ensures that indices will be employed when executing the IN clause.

For instance, the following query performs a range search with an index on the 'id' column:

explain 
select * from ratings where id in (2331425, 430364, 4557546, 2696638, 4510549, ...)

Precautions and Caveats

While this approach allows for efficient indexed queries, it is crucial to consider the following precautions:

• SQL Injection Attacks: Using user-supplied data in stored procedures can open avenues for SQL injection attacks. Proper sanitization is essential to prevent malicious code execution.
• Tablescans: If the column used for the range search is not indexed or has non-null values, it can result in a dreaded table scan, which is highly inefficient for large datasets.

The above is the detailed content of How Can I Make MySQL's FIND_IN_SET Function Utilize Indices?. For more information, please follow other related articles on the PHP Chinese website!