Shulou(Shulou.com)06/01 Report--

Query background

There is a table tmp_test_course with about 100000 records, and then there is a json field called outline, which stores one-to-many relationships (multiple encodings, such as jy1577683381775).

We need to retrieve a specific type of data from these 100000 pieces of data. The total target data volume: 2931 items.

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

While limiting to the above type, we also have to include any of the following encodings (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

Four ways to query outline fields are listed below, and the corresponding query time and the number of rows scanned are given

1. Like query

Take 248 milliseconds

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%')

The results of EXPLAIN analysis are as follows: full table scan

Second, json function query

Json official function

It takes 196 milliseconds, which is a little faster.

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

The results of EXPLAIN analysis are as follows, or full table scan.

Third, joint index query

The following is to create a federated index for the table (I originally wanted to create an index of type-del-is_leaf-outline, but the outline field is too long, so only add the federated index of type-del-is_leaf

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

After adding the index, the like and json queries are executed, which is significantly faster.

It takes 136ms for like execution and 82.6ms for json query, which shows that using json function query for json type is faster than like.

The results of EXPLAIN analysis are as follows, and the number of rows scanned by both queries is limited to 2931 rows.

IV. Full-text index query

Since full-text indexing only supports 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 let's use full-text index for retrieval.

SELECT * FROM tmp_test_course WHERE `type` = 5 AND del=2 AND is_leaf=1AND 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 improvement is extremely obvious, which shows the strength of the full-text index.

The results of EXPLAIN analysis are as follows, showing that only one line has been scanned

Conclusion

The following are the implementation results of four cases

Full-text index: 11.6ms

Joint index: 82.6ms (json), 136ms (like)

Json function query: 196ms

Like query: 248ms

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 volume of 100000, the query speed is about 20 times faster than that of direct query. If it is a million or tens of millions of tables, the promotion gap will be even greater, so it is better to honestly use full-text indexing if possible.

So far, this article on MySQL full-text index, joint index, like query, json query speed which is introduced here, more related mysql full-text index joint index like query json query content please search previous articles or continue to browse the following related articles hope that you will support more in the future!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.