For fuzzy query of the mysqlset field type, there are 0.4 million testing data tables: flag & nbsp; set ('R', & nbsp; 'L', & nbsp; 'C ', & nbsp; 'P') & nbsp; SELECT & nbsp;. & nbsp; * & nbsp;, & nbsp; B. typedirFROM & nbsp; mzrui_arch about fuzzy query of mysql set field types
There are 0.4 million test data tables
Flag set ('R', 'L', 'C', 'P ')
SELECT a. *, B. typedir
FROM mzrui_archives
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
It takes 2.5 seconds to query this statement. after removing like, the query is quite fast and I don't know how to optimize it.
Uid is the primary key
Key kid (kid, flag) Index
Share:
------ Solution --------------------
Since it is set, why like query? Find_in_set ('P', a. flag)
------ Solution --------------------
Reference:
Quote: reference:
Since it is set, why like query? Find_in_set ('P', a. flag)
The efficiency of find_in_set is the same as that of find_in_set. The key is that find_in_set cannot ('c, P', a. flag) multiple conditions, so that I cannot find the record.
You can
find_in_set('p',a.flag) and find_in_set('c',a.flag)
------ Solution --------------------
If you only have four identifiers ('R', 'L', 'C', and 'P'), use 1 2 4 8 as the identification space, for example, 1
------ Solution --------------------
2 = 3 to identify the flag. you can use the where flag & 2 method during query, which should be much faster.
------ Solution --------------------
For the set type field find_in_set, bitwise operations are used.
However, like '% l %' is definitely not desirable. if it is like '% l, r %' or like '% l, p % 'cannot be found at all?
Of course, whether like or find_in_set is to traverse the entire table, otherwise you do not know which record can match
The set type is stored by long integer, and the index may be faster.
