Fuzzy query problem about MySQL set field type

There's a 400,000-piece test data sheet.
Flag set (' R ', ' L ', ' C ', ' P ')

SELECT A. *, B.typedir
From Mzrui_archives A
Left JOIN mzrui_kind b on a.kid = B.uid
WHERE a.flag like '%p% '
and A.kid
In (3, 17, 18)
ORDER by A.uid
LIMIT 0, 15

This statement query takes 2.5 seconds, the like removed after the query pretty fast, do not know how to optimize, ask.

UID is primary key
Key Kid (Kid,flag) Index

Reply to discussion (solution)

Since it is set, why do you want a like query? Find_in_set (' P ', A.flag)

Since it is set, why do you want a like query? Find_in_set (' P ', A.flag)

Find_in_set efficiency is the same, I tested, the key is Find_in_set can't (' c,p ', A.flag) multi-condition, so I can not find records to

Index is not a problem

Since it is set, why do you want a like query? Find_in_set (' P ', A.flag)

Find_in_set efficiency is the same, I tested, the key is Find_in_set can't (' c,p ', A.flag) multi-condition, so I can not find records to

You can

Find_in_set (' P ', A.flag) and Find_in_set (' C ', A.flag)

Since it is set, why do you want a like query? Find_in_set (' P ', A.flag)

Find_in_set efficiency is the same, I tested, the key is Find_in_set can't (' c,p ', A.flag) multi-condition, so I can not find records to

You can

Find_in_set (' P ', A.flag) and Find_in_set (' C ', A.flag)

Yes, but the efficiency is the same, I am now concerned about the efficiency problem, there is no way to improve efficiency

Find_in_set will scan the whole table, the efficiency is really not high.
You can consider changing the structure of your table.
Create another intermediate table to store the contents of the set. Each time you make a connection query and do a good job of indexing the fields, the efficiency should be slightly better.

If you only have 4 logos (' R ', ' L ', ' C ', ' P '), it is better to use 1 2 4 8 as the identity bit, to identify flag with a way such as 1|2 = 3, when querying can be where flag & 2 way, should be much faster

If you only have 4 logos (' R ', ' L ', ' C ', ' P '), it is better to use 1 2 4 8 as the identity bit, to identify flag with a way such as 1|2 = 3, when querying can be where flag & 2 way, should be much faster

You can not blur the query Ah, for example, I have a record of 1,2,4 so that the value should be saved is 7
I want to query the records containing 2, where flag & 2 can not query it????

If you only have 4 logos (' R ', ' L ', ' C ', ' P '), it is better to use 1 2 4 8 as the identity bit, to identify flag with a way such as 1|2 = 3, when querying can be where flag & 2 way, should be much faster

I tested the WHERE flag & 2 effect is similar to the like '%l% ' effect and has no improvement in efficiency.

For a set Type field Find_in_set is a bitwise operation
But like '%l% ' this certainly is not advisable, if is the kind '%l,r% ' or like '%l,p% ' not to find anything?
Of course, both like and Find_in_set are going to traverse the entire table, or you don't know which record matches the
The set type is stored by long shaping, and the index may be faster

For a set Type field Find_in_set is a bitwise operation
But like '%l% ' this certainly is not advisable, if is the kind '%l,r% ' or like '%l,p% ' not to find anything?
Of course, both like and Find_in_set are going to traverse the entire table, or you don't know which record matches the
The set type is stored by long shaping, and the index may be faster

Yes, you're right. I added this key kid (kid,flag) composite index, do not know this index is right, is currently 2.5 seconds or so of query time, I want to optimize to 0.0 seconds or less. There's no other way to change the table structure.

For a set Type field Find_in_set is a bitwise operation
But like '%l% ' this certainly is not advisable, if is the kind '%l,r% ' or like '%l,p% ' not to find anything?
Of course, both like and Find_in_set are going to traverse the entire table, or you don't know which record matches the
The set type is stored by long shaping, and the index may be faster

Yes, you're right. I added this key kid (kid,flag) composite index, do not know this index is right, is currently 2.5 seconds or so of query time, I want to optimize to 0.0 seconds or less. There's no other way to change the table structure.

Like "%xxx" this form of query: Because the index is basically a B-tree or a + + tree, the index is not used

Like and Find_in_set will traverse the entire table on the query. The data for about 400,000 may be 2.5 seconds, but later if the data is more then millions, it will be slower, and neither of them will use the index, so if you want to improve efficiency, you can only see if you can improve the SQL based on business needs.

You can also EXPLAIN see MySQL's advice

ID select_type table Type possible_keys key Key_len ref rows Extra
1 Simple a index kid PRIMARY 4 NULL Using where
1 Simple b eq_ref PRIMARY PRIMARY 3 mzrui.a.kid 1


Use explain to parse out is like this, have what optimization suggestion?

