Which is faster among MySQL full-text index, joint index, like query, and json query?

Which is faster among MySQL full-text index, joint index, like query, and json query?

Query Background

There is a table tmp_test_course with about 100,000 records, and then there is a json field called outline, which stores a one-to-many relationship (saves multiple codes, such as jy1577683381775)

We need to retrieve specific types of data from these 100,000 records. The total target data volume is: 2931 records

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

While limiting the above types, we must also include any of the following codes (that is, OR query)

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

The following lists four ways to query the outline field, giving the corresponding query time and number of scanned rows.

1. Like query

It took 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 EXPLAIN analysis results are as follows: Full table scan


2. JSON function query

json official function

It took 196 milliseconds, which is 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  
)

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


3. Joint Index Query

Next, create a joint 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 I only added a joint index of type-del-is_leaf

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

After adding the index, the like and json queries are significantly accelerated.
Like execution took 136 milliseconds, and json query took 82.6 milliseconds. This shows that using json function query for json type is faster than like



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



4. 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 let's use the full-text index to search

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 is significantly improved, which shows the power of full-text indexing.

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


in conclusion

The following are the execution results of 4 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 faster the full-text indexing speed is. For a table with 100,000 data points, the query speed is about 20 times faster than direct query. If the table has millions or tens of millions of data points, the improvement will be even greater. So if possible, use full-text indexing.

This is the end of this article about which is faster, MySQL full-text index, combined index, like query, or json query. For more information about mysql full-text index, combined index, like query, and json query, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySql fuzzy query json keyword retrieval solution example
  • Example code for converting Mysql query result set into JSON data
  • Detailed explanation of querying JSON format fields in MySQL
  • MySQL json format data query operation
  • Summary of related functions for Mysql query JSON results
  • Two query methods when the MySQL query field type is json
  • Python query mysql, return json instance
  • How to query json in the database in mysql5.6 and below
  • Mysql directly queries the data in the stored Json string

<<:  Vue implements form validation function

>>:  Study notes to write the first program of Vue

Recommend

Let you understand how HTML and resources are loaded

All content in this blog is licensed under Creati...

In-depth understanding of the implementation principle of require loader

Preface We often say that node is not a new progr...

Detailed steps to install nginx on Apple M1 chip and deploy vue project

brew install nginx Apple Mac uses brew to install...

Detailed introduction of Chrome developer tools-timeline

1. Overview Users expect the web applications the...

Deploy Varnish cache proxy server based on Centos7

1. Varnish Overview 1. Introduction to Varnish Va...

Share 12 commonly used Loaders in Webpack (Summary)

Table of contents Preface style-loader css-loader...

Detailed example of using the distinct method in MySQL

A distinct Meaning: distinct is used to query the...

...

Detailed explanation of server and location configuration of simple forwarding request of nginx

Let's briefly sort out the configuration of s...

How to try to add sticky effect to your CSS

Written in front I don’t know who first discovere...

Solution to the ineffectiveness of flex layout width in css3

Two-column layout is often used in projects. Ther...

How to use shell to perform batch operations on multiple servers

Table of contents SSH protocol SSH Connection pro...

Analysis of how to create a stored procedure in MySQL to add new fields to a data table

This article uses an example to describe how to c...

Detailed explanation of Vue3's sandbox mechanism

Table of contents Preface Browser compiled versio...

Semantics: Is Html/Xhtml really standards-compliant?

<br />Original text: http://jorux.com/archiv...