是否使用FIND_IN_SET()? [英] FIND_IN_SET() alternative?

问题描述

我有一个当前看起来像的查询:

I have a query that currently looks like:

SELECT [column a], [column b], [column c], [column d]
FROM [table] 
WHERE FIND_IN_SET(2, column d)
ORDER BY [column a] DESC

[列d]的类型为varchar，其中包含一组数字(例如，3, 2, 4, 6, 1, 9).因此，基本上我想返回所有2在其数字集中的记录.但是，当我对上述查询执行EXPLAIN时，这是我的输出:

Where [column d] is of type varchar, and holds a set of numbers (ex, 3, 2, 4, 6, 1, 9). So basically I am trying to return all records where 2 is in its set of numbers. However, when I execute an EXPLAIN on the above query, this is my output:

id  select_type table   type    possible_keys   key     key_len     ref     rows        Extra
1   SIMPLE      [table] ALL     NULL            NULL    NULL        NULL    500000      Using where; Using filesort

在执行此查询期间，该查询似乎未使用任何索引. [column a]是主键，因此该列上已经有一个索引.有什么办法可以利用索引来使查询更快地运行吗?还是我可以通过另一种方法来提高此查询的性能?

This query does not seem to be using any indices during the execution of this query. [column a] is the primary key, so there is an index on that column already. Is there any way to utilize an index for this query to run faster? Or is there another way for me to improve the performance of this query?

推荐答案

替代:正确规范化模式.

Alternative: properly normalize the schema.

FIND_IN_SET是 not 可修改，索引不能.

FIND_IN_SET is not Sargable and the index cannot be used.

这篇关于是否使用FIND_IN_SET()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！