Query Background

There is a table, tmp_test_course Around 100,000 records, and then a json field, outline, that holds a one-to-many relationship (multiple codes, such as jy15776881775)

We need to retrieve specific types of data in this 100,000 data set, with a total target of 2,931

SELECT COUNT(*) FROM tmp_test_course WHERE `type`=5 AND del=2 AND is_leaf=1

While limiting to the above type, we must also include any of the following codes (that is, OR queries)

jy1577683381775
jy1577683380808
jy1577683379178
jy1577683378676
jy1577683377617
jy1577683376672
jy1577683375903
jy1578385720787
jy1499916986208
jy1499917112460
jy1499917093400
jy1499917335579
jy1499917334770
jy1499917333339
jy1499917331557
jy1499917330833
jy1499917329615
jy1499917328496
jy1576922006950
jy1499916993558
jy1499916992308
jy1499917003454
jy1499917002952

Here are four ways to query outline fields, giving the query time and the number of rows scanned

1. like Query

248 MS elapsed

SELECT * FROM tmp_test_course 
WHERE `type`=5 AND del=2 AND is_leaf=1 
AND (
outline like '%jy1577683381775%'
OR outline like '%jy1577683380808%'
OR outline like '%jy1577683379178%'
OR outline like '%jy1577683378676%'
OR outline like '%jy1577683377617%'
OR outline like '%jy1577683376672%'
OR outline like '%jy1577683375903%'
OR outline like '%jy1578385720787%'
OR outline like '%jy1499916986208%'
OR outline like '%jy1499917112460%'
OR outline like '%jy1499917093400%'
OR outline like '%jy1499917335579%'
OR outline like '%jy1499917334770%'
OR outline like '%jy1499917333339%'
OR outline like '%jy1499917331557%'
OR outline like '%jy1499917330833%'
OR outline like '%jy1499917329615%'
OR outline like '%jy1499917328496%'
OR outline like '%jy1576922006950%'
OR outline like '%jy1499916993558%'
OR outline like '%jy1499916992308%'
OR outline like '%jy1499917003454%'
OR outline like '%jy1499917002952%'
)

EXPLAIN analysis results are as follows, full table scan

2. json function query

With the function JSON_SEARCH, see more functions MySQL Official Documentation

As you can see, the query took 196 milliseconds, slightly faster

SELECT * FROM tmp_test_course 
WHERE `type`=5 AND del=2 AND is_leaf=1
AND 
(
JSON_SEARCH(outline, 'one', 'jy1577683381775') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1577683380808') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1577683379178') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1577683378676') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1577683377617') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1577683376672') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1577683375903') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1578385720787') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499916986208') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499917112460') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499917093400') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499917335579') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499917334770') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499917333339') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499917331557') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499917330833') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499917329615') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499917328496') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1576922006950') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499916993558') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499916992308') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499917003454') IS NOT NULL OR
JSON_SEARCH(outline, 'one', 'jy1499917002952') IS NOT NULL   
)

EXPLAIN analysis results as follows, or full table scan

3. Joint Index Query

Here's a union index for the table (you wanted to create a type-del-is_leaf-outline index, but the outline field is too long to limit, so only the type-del-is_leaf union index is added

ALTER TABLE tmp_test_course ADD KEY `type-del-is_leaf` (`type`,`del`,`is_leaf`)

The like s and json queries are executed after the index is added, which increases the speed significantly.
Likes took 136 milliseconds to execute and json queries 82.6 milliseconds, so it's faster to use a json function query for a json type than like s

The results of the EXPLAIN analysis are as follows, both query scans are limited to 2931 rows

IV. Full-text Index Query

Because full-text indexes only support CHAR, VARCHAR, and TEXT, we need to change the JSON field definition

ALTER TABLE tmp_test_course MODIFY `outline` VARCHAR(1024) NOT NULL DEFAULT '[]'

Add Full-Text Index

ALTER TABLE tmp_test_course ADD FULLTEXT INDEX outline (outline);

Now come back to full-text indexing

SELECT * FROM tmp_test_course 
WHERE `type`=5 AND del=2 AND is_leaf=1
AND 
MATCH(outline) AGAINST ('jy1577683381775 jy1577683380808 jy1577683379178 jy1577683378676 jy1577683377617 jy1577683376672 jy1577683375903 jy1578385720787 jy1499916986208 jy1499917112460 jy1499917093400 jy1499917335579 jy1499917334770 jy1499917333339 jy1499917331557 jy1499917330833 jy1499917329615 jy1499917328496 jy1576922006950 jy1499916993558 jy1499916992308 jy1499917003454 jy1499917002952')

It takes 11.6 milliseconds, and the speed increase is remarkable, so the full-text index is impressive.

The results of EXPLAIN analysis are as follows, showing that only one row was scanned

conclusion

The following are the results of four scenarios

Conclusion: Full-text index > Joint index > JSON function query > like query
The larger the amount of data, the more obvious the speed of full-text indexing. With the amount of 100,000, the query speed is about 20 times faster than direct query. If it is a table of millions or millions level, the gap will increase, so it is better to be honest and practical.