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

Use Html+Css to implement a simple navigation bar function (the navigation bar switches the background color when the mouse is encountered)

Ⅰ. Problem description: Use html+css to implement...

JavaScript to achieve a simple magnifying glass effect

There is a picture in a big box. When you put the...

React entry-level detailed notes

Table of contents 1. Basic understanding of React...

Are you still Select *?

There are many reasons why an application is as s...

Detailed explanation of how Angular handles unexpected exception errors

Written in front No matter how well the code is w...

Use native js to simulate the scrolling effect of live bullet screen

Table of contents 1. Basic principles 2. Specific...

Explanation of using if judgment conditions in sum and count functions when using SQL statements to collect data

First, let me give you an example (if you don’t w...

Analysis of the principle of centering elements with CSS

It is a very common requirement to set the horizo...

Collection of 25 fonts used in famous website logos

This article collects the fonts used in the logos...

Solution to MySQL startup successfully but not listening to the port

Problem Description MySQL is started successfully...

How to use type enhancement without typingscript

Preface Due to the weak typing of JS, loose writi...

The meaning and calculation method of QPS and TPS of MySQL database

When doing DB benchmark testing, qps and tps are ...

MySQL5.7 parallel replication principle and implementation

Anyone who has a little knowledge of data operati...

MySQL database design: detailed explanation of Schema operation method using Python

Before the arrow was shot, the bow whispered to t...

Nginx server https configuration method example

Linux: Linux version 3.10.0-123.9.3.el7.x86_64 Ng...