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

The sorting of mysql 5.6is optimized. The same sql may get different results on 5.5and 5.6:

CREATE TABLE `TestCase2` (

`id` bigint (20) NOT NULL AUTO_INCREMENT

`aValue` decimal (19recover2) NOT NULL

`accuracyClassType_ id` bigint (20) NOT NULL

`productType_ id` bigint (20) NOT NULL

PRIMARY KEY (`id`)

KEY `FKF58064BD791396` (`FKF58064BD791396`)

KEY `FKF58064BDED5076` (`FKF58064BDED5076`)

) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8

INSERT INTO `TestCase2` (`id`, `aValue`, `TestCase2`, `TestCase2`, `productType_ id`)

VALUES

(1, 2.00, 3, 2)

(2pr 3.00pr 3pr 2)

(3, 4.00, 2, 3)

(4, 5.00, 2, 3)

(5, 6. 00, 2, 3)

(6, 8.00, 2, 3)

(7, 10.00, 2, 3)

(8, 12.00, 2, 3)

(9, 16.00, 2, 3)

(10, 20.00, 2, 3)

(11pr 6.00pr 2pr 4)

(12, 8.00, 2, 4)

(13, 10.00, 2, 4)

(14, 12.00, 2, 4)

(15pr. 5.00pr. 2pr 2)

(16pr 6.00pr 2pr 2)

Select * from Testcase2 order by aValue desc

# as you can see mysql has added a fallback sorting because aValue is not unique, which is ok

# the first four id's of the results are: 10, 9, 14, 8

Select * from Testcase2 order by aValue desc limit 4

# as expected the result id's (based on the order by) are: 10, 9, 14, 8

Select * from Testcase2 order by aValue desc limit 3

# which surprisingly results in the following id's based on the order by: 10, 9, 8?

# expecting id's: 10,9,14 (see query with limit 4) [19 Mar 2014 13:34] Miguel SolorzanoThank you for the bug report.

Mysql 5.1 > select * from Testcase2 order by aValue desc limit 4

+-+

| | id | aValue | accuracyClassType_id | productType_id | |

+-+

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 14 | 12.00 | 2 | 4 |

| | 8 | 12.00 | 2 | 3 | |

+-+

4 rows in set (0.00 sec)

Mysql 5.1 > select * from Testcase2 order by aValue desc limit 3

+-+

| | id | aValue | accuracyClassType_id | productType_id | |

+-+

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 14 | 12.00 | 2 | 4 |

+-+

3 rows in set (0.00 sec)

Mysql 5.6 > select * from Testcase2 order by aValue desc limit 4

+-+

| | id | aValue | accuracyClassType_id | productType_id | |

+-+

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 14 | 12.00 | 2 | 4 |

| | 8 | 12.00 | 2 | 3 | |

+-+

4 rows in set (0.00 sec)

Mysql 5.6 > select * from Testcase2 order by aValue desc limit 3

+-+

| | id | aValue | accuracyClassType_id | productType_id | |

+-+

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 8 | 12.00 | 2 | 3 | |

+-+

3 rows in set (0.00 sec)

The implementation of sorting in versions 5.5 and 5.6 of mysql:

Mysql 5.5 willl

-allocate buffer space for 1000 rows

-scan the table, inserting each row into the buffer

-sort the buffer, this requires about 10.000 comparisons

-return the first 3 (or 4) rowsMysql

5.6 will

-allocate buffer space for 3 (or 4) rows

-scan the table, maintaining a collection of 3 (or 4) "winners" this requires about 2000 comparisons

-sort final set of "winners", this requires about 6 comparisons

-return the result

In the sql standard, there is no definition of which row of records should be issued for the sorting field of repeated values, which depends entirely on the implementation of the code.

Our upgrade test may involve a comparison of 5.5 and 5.6 results, which may be questionable.

This is normal. If you do need strict sorting, achieve 5.6 and 5.5 the same display results.

You need to modify the sql statement by adding a unique field to the sort clause.

Reference:

Https://bugs.mysql.com/bug.php?spm=5176.100239.blogcont27649.4.tlI76p&id=72076

Http://didrikdidrik.blogspot.co.uk/

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.