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

Create table T1 (

A1 bigint not null primary key auto_increment

C10 char (10)

C100 char (100)

C200 char (200)

C30 char (255)

C300 text

)

Insert into T1 (c10, c100, and c200, c30, and c300) values.

Insert into T1 (c10 and c100), c200 and c30) select lpad, lpad (c100 and 100), lpad (c200 and 190), lpad (c30 and 250), lpad (c300 and 2000) and from T1

Root@localhost [test] > select count (A1) from T1

+-+

| | count (A1) |

+-+

| | 65536 |

Select SQL_NO_CACHE count (1) from (

Select A1 from T1 IGNORE INDEX (primary) order by c10 desc limit 1000) a

Select SQL_NO_CACHE count (1) from (

Select A1 IGNORE INDEX c100 from T1 IGNORE INDEX (primary) order by c10 desc limit 1000) a

Select SQL_NO_CACHE count (1) from (

Select A1 IGNORE INDEX c100 from T1 IGNORE INDEX (primary) order by c10 desc limit 1000) a

Select SQL_NO_CACHE count (1) from (

Select A1, c100, c200, from T1 IGNORE INDEX (primary) order by c10 desc limit 1000) a

Select SQL_NO_CACHE count (1) from (

Select A1 IGNORE INDEX c100 desc limit c200 IGNORE INDEX c300 from T1 IGNORE INDEX (primary) order by c10 desc limit 1000) a

Select SQL_NO_CACHE count (1) from (

Select * from T1 IGNORE INDEX (primary) order by c10 desc limit 1000) a

From the above experimental results, it is only performed once, and if the relatively accurate value is obtained, it can be executed several more times.

But we can see from the top and the last one that one is in 3.* seconds and the other is in 6.* seconds.

It shows that when there is sorting, it is not only the columns behind the order by that affect performance, because they all use the tmp table, and the contents of the tmp table are the columns in the select, and then sort according to the columns in the order by, so the tmp values are select col and * rows.

You can also find from show profile that Creating sort index is the executive performance focus above, which further demonstrates the above point of view.

Please correct the inadequacies.

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.