FIND_IN_SET () alternative?

Question

I have a query that currently looks like this:

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

Where [column d] is of type varchar and contains a set of numbers (ex, 3, 2, 4, 6, 1, 9 ). So basically I try to return all records where 2 is in my set of numbers. However, when I execute EXPLAIN in 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 

This query does not seem to use any indexes during the execution of this query. [column a] is the primary key, so there is an index in this column. Is there a way to use the index for this query to work faster? Or is there another way to improve the performance of this request?

Answer 1

Alternative: properly normalize the circuit.

FIND_IN_SET is not Sargable , and the index cannot be used.

Answer 2

One possible optimization is to define [column d] as the type of SET . As the documentation says :

MySQL stores the SET values ​​numerically, with the least significant bit being the stored value corresponding to the first set. If you are SET in a digital context, the resulting value has bits set corresponding to the given members that make up the column value.

Here is a quick and easy example:

 CREATE TABLE `tbl_name` ( `id` int(11) NOT NULL AUTO_INCREMENT, `set_col` set('a','b','c','d') NOT NULL, PRIMARY KEY (`id`), KEY `set_col_idx` (`set_col`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `tbl_name` (`set_col`) VALUES ('a'), ('b'), ('c'), ('d'), ('a,b'), ('a,c'), ('a,d'), ('b,c'), ('b,d'), ('c,d'), ('a,b,c'), ('a,b,d'), ('a,c,d'), ('b,c,d'), ('a,b,c,d'); 

The set_col column specified as SET('a','b','c','d') has members with the following decimal and binary values:

 ╔════════════╦═══════════════╦══════════════╗ ║ SET Member ║ Decimal Value ║ Binary Value ║ ╠════════════╬═══════════════╬══════════════╣ ║ 'a' ║ 1 ║ 0001 ║ ╠════════════╬═══════════════╬══════════════╣ ║ 'b' ║ 2 ║ 0010 ║ ╠════════════╬═══════════════╬══════════════╣ ║ 'c' ║ 4 ║ 0100 ║ ╠════════════╬═══════════════╬══════════════╣ ║ 'd' ║ 8 ║ 1000 ║ ╚════════════╩═══════════════╩══════════════╝ 

So, if you need to extract records with the value a,c,d , this is the first, third and fourth member, which is 1 + 4 + 8 , which is 13 .

If you run the query:

 EXPLAIN SELECT * FROM `tbl_name` WHERE `tbl_name`.`set_col` = 13; 

You'll get:

 ╔════╦═════════════╦══════════╦══════╦═══════════════╦═════════════╦═════════╦═══════╦══════╦═════════════╗ ║ id ║ select_type ║ table ║ type ║ possible_keys ║ key ║ key_len ║ ref ║ rows ║ Extra ║ ╠════╬═════════════╬══════════╬══════╬═══════════════╬═════════════╬═════════╬═══════╬══════╬═════════════╣ ║ 1 ║ SIMPLE ║ tbl_name ║ ref ║ set_col_idx ║ set_col_idx ║ 1 ║ const ║ 1 ║ Using index ║ ╚════╩═════════════╩══════════╩══════╩═══════════════╩═════════════╩═════════╩═══════╩══════╩═════════════╝ 

You do not need to manually find the decimal values ​​of the SET parameters - you can use SUM , for example:

 SELECT * FROM `tbl_name` WHERE `set_col` = (SELECT SUM(`set_col`) FROM `tbl_name` WHERE `set_col` IN ('a', 'c', 'd') ); 

 ╔════╦═════════════╦══════════╦═══════╦═══════════════╦═════════════╦═════════╦═══════╦══════╦══════════════════════════╗ ║ id ║ select_type ║ table ║ type ║ possible_keys ║ key ║ key_len ║ ref ║ rows ║ Extra ║ ╠════╬═════════════╬══════════╬═══════╬═══════════════╬═════════════╬═════════╬═══════╬══════╬══════════════════════════╣ ║ 1 ║ PRIMARY ║ tbl_name ║ index ║ set_col_idx ║ set_col_idx ║ 1 ║ NULL ║ 15 ║ Using where; Using index ║ ╠════╬═════════════╬══════════╬═══════╬═══════════════╬═════════════╬═════════╬═══════╬══════╬══════════════════════════╣ ║ 1 ║ SUBQUERY ║ tbl_name ║ range ║ set_col_idx ║ set_col_idx ║ 1 ║ NULL ║ 3 ║ Using where; Using index ║ ╚════╩═════════════╩══════════╩═══════╩═══════════════╩═════════════╩═════════╩═══════╩══════╩══════════════════════════╝ 

Answer 3

1- a full-text index is not a good idea in this case, because: the length of the string you are looking for is small (1) in this case, and it will not be found (it is customizable, although it is not a good idea)

2- If this query is frequent, I suggest changing the table structure as follows:

• table1 (col_a PK, col_b, col_c)
• table2 (col_a FK, value_of_sub_d), where value_of_sub_d is one of (2, 3, ...)

In this one-to-many relationship, you can either make a connection or get PK from table2, where the condition is satisfied, and select this ID row from table 1 Example:

 Select * from table1 t1 inner join table2 t2 on t1.col_a=t2.col_a WHERE t2.value_of_sub_d=2