Shulou(Shulou.com)05/31 Report--

This article will explain in detail how Mysql consumes like operations on the LONGTEXT field, and the content of the article is of high quality, so the editor will share it for you to do a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

# Mysql 5140 @ RHEL 5u4 X86room64

# provide some table information first:

=

Root@127.0.0.1: trac_apsara 17:18:46 > show create table wiki G

* * 1. Row *

Table: wiki

Create Table: CREATE TABLE `wiki` (

`name` longtext COLLATE utf8_bin

`version`int (11) DEFAULT NULL

`time`bigint (20) DEFAULT NULL

`author` longtext COLLATE utf8_bin

`ipnr` longtext COLLATE utf8_bin

`text`longtext COLLATE utf8_bin

`comment` longtext COLLATE utf8_bin

`readonly` int (11) DEFAULT NULL

KEY `wiki_time_ idx` (`time`)

KEY `name_ver_ ind` (`name`, `version`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

1 row in set (0.00 sec)

Root@127.0.0.1: trac_apsara 17:19:04 > select count (*) from wiki

+-+

| | count (*) |

+-+

| | 76514 |

+-+

1 row in set (0.03 sec)

Root@127.0.0.1: trac_apsara 17:19:08 > select count (distinct name) from wiki

+-- +

| | count (distinct name) |

+-- +

| | 40369 |

+-- +

1 row in set (0.59 sec)

Root@127.0.0.1: trac_apsara 17:19:21 > show variables like 'innodb_buffer%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_buffer_pool_size | 1073741824 | |

+-+ +

1 row in set (0.00 sec)

Root@127.0.0.1: trac_apsara 17:21:08 > show table status like 'wiki' G

* * 1. Row *

Name: wiki

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 336009

Avg_row_length: 4458

Data_length: 1498120192

Max_data_length: 0

Index_length: 10551296

Data_free: 7340032

Auto_increment: NULL

Create_time: 2010-09-29 14:49:20

Update_time: NULL

Check_time: NULL

Collation: utf8_bin

Checksum: NULL

Create_options:

Comment:

1 row in set (0.01 sec)

=

# Let's take a look at SQL and data:

# # SQL1:

SELECT w1.name,w1.time,w1.author,w1.text

FROM wiki w1

(SELECT name,max (version) AS ver FROM wiki GROUP BY name) w2

WHERE w1.version = w2.ver AND w1.name = w2.name

AND (w1.name LIKE'% RpcRequestPtr%' ESCAPE'/'

OR w1.author LIKE'% RpcRequestPtr%' ESCAPE'/'

OR w1.text LIKE'% RpcRequestPtr%' ESCAPE'/'

);

# # SQL2:

SELECT w1.name,w1.time,w1.author,w1.text

FROM wiki w1

(SELECT name,max (version) AS ver FROM wiki GROUP BY name) w2

WHERE w1.version = w2.ver AND w1.name = w2.name

AND (w1.name LIKE'% RpcRequestPtr%' ESCAPE'/'

OR w1.author LIKE'% RpcRequestPtr%' ESCAPE'/'

# OR w1.text LIKE'% RpcRequestPtr%' ESCAPE'/'

);

The difference between two SQL and only one WHERE condition.

Root@127.0.0.1: trac_apsara 17:24:08 > explain SELECT w1.namerecoveryw1.timerecoveryw1.authorleglegw1.text

-> FROM wiki W1

-> (SELECT name,max (version) AS ver FROM wiki GROUP BY name) w2

-> WHERE w1.version = w2.ver AND w1.name = w2.name

-> AND (w1.name LIKE'% RpcRequestPtr%' ESCAPE'/'

-> OR w1.author LIKE'% RpcRequestPtr%' ESCAPE'/'

-> OR w1.text LIKE'% RpcRequestPtr%' ESCAPE'/'

->)

+- -+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+- -+

| | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 40369 |

| | 1 | PRIMARY | W1 | ref | name_ver_ind | name_ver_ind | 608 | w2.namery w2.ver | 3 | Using where |

| | 2 | DERIVED | wiki | ALL | NULL | NULL | NULL | NULL | 445724 | Using temporary; Using filesort |

+- -+

3 rows in set (1.04 sec)

Root@127.0.0.1: trac_apsara 17:22:26 > explain SELECT w1.namerecoveryw1.timerecoveryw1.authorleglegw1.text

-> FROM wiki W1

-> (SELECT name,max (version) AS ver FROM wiki GROUP BY name) w2

-> WHERE w1.version = w2.ver AND w1.name = w2.name

-> AND (w1.name LIKE'% RpcRequestPtr%' ESCAPE'/'

-> OR w1.author LIKE'% RpcRequestPtr%' ESCAPE'/'

-> # OR w1.text LIKE'% RpcRequestPtr%' ESCAPE'/'

->)

+- -+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+- -+

| | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 40369 |

| | 1 | PRIMARY | W1 | ref | name_ver_ind | name_ver_ind | 608 | w2.namery w2.ver | 3 | Using where |

| | 2 | DERIVED | wiki | ALL | NULL | NULL | NULL | NULL | 445724 | Using temporary; Using filesort |

+- -+

3 rows in set (1.03 sec)

# from the point of view of the execution plan, the two SQL are exactly the same; the number of rows processed is also the same

Root@127.0.0.1: trac_apsara 17:25:39 > reset query cache

Query OK, 0 rows affected (0.00 sec)

Root@127.0.0.1: trac_apsara 17:25:52 > SELECT w1.namerecoveryw1.timerecoveryw1.authorleglegw1.text

-> FROM wiki W1

-> (SELECT name,max (version) AS ver FROM wiki GROUP BY name) w2

-> WHERE w1.version = w2.ver AND w1.name = w2.name

-> AND (w1.name LIKE'% RpcRequestPtr%' ESCAPE'/'

-> OR w1.author LIKE'% RpcRequestPtr%' ESCAPE'/'

-> # OR w1.text LIKE'% RpcRequestPtr%' ESCAPE'/'

->)

Empty set (1.31 sec)

Root@127.0.0.1: trac_apsara 17:26:12 > reset query cache

Query OK, 0 rows affected (0.00 sec)

Root@127.0.0.1: trac_apsara 17:26:15 > SELECT w1.namerecoveryw1.timerecoveryw1.authorleglegw1.text

-> FROM wiki W1

-> (SELECT name,max (version) AS ver FROM wiki GROUP BY name) w2

-> WHERE w1.version = w2.ver AND w1.name = w2.name

-> AND (w1.name LIKE'% RpcRequestPtr%' ESCAPE'/'

-> OR w1.author LIKE'% RpcRequestPtr%' ESCAPE'/'

-> OR w1.text LIKE'% RpcRequestPtr%' ESCAPE'/'

->)

13 rows in set (3.50 sec)

# # in terms of execution time

# # SQL1: 3.50 sec, SQL2: 1.31 sec

# # We can basically tell from this that MYSQL uses 2.19 sec to process 40369 LIKE fuzzy query operations of TEXT fields in memory.

# # while querying 40369 times from the WIKI table INDEX, it only took 1.31 seconds (maybe less). Of course, the data is already in CACHE.

# # We should pay attention to SQL in the future. Not only does it take time to read the hard disk, but the LIKE fuzzy query operation in memory is also time-consuming.

On Mysql in the LONGTEXT field to do like operation consumption is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.