Geek Answers Handbook

Why can't MySQL use index for ORDER BY if mixed ASC and DESC?

Let's say I have a very simple table:

CREATE TABLE `t1` (
  `key_part1` INT UNSIGNED NOT NULL,
  `key_part2` INT UNSIGNED NOT NULL,
  `value` TEXT NOT NULL,
  PRIMARY KEY (`key_part1`, `key_part2`)
) ENGINE=InnoDB

Using this table, I want to issue the following query:

SELECT *
FROM `t1`
ORDER BY `key_part1` ASC, `key_part2` DESC
LIMIT 1

I was hoping that ORDER BYin this query I would be satisfied with the index. However, according to the MySQL documentation :

In some cases, MySQL cannot use indexes to resolve ORDER BY, although it still uses indexes to find rows that match the sentence WHERE. These cases include the following:

  • You mix ASCand DESC:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

I tried a request similar to the above request, and, as expected, the output EXPLAINsays that such a request has a file port. This makes no sense to me, because I can do the following:

SELECT *
FROM `t1`
WHERE `key_part1` = (
    SELECT `key_part1`
    FROM `t1`
    ORDER BY `key_part1` ASC
    LIMIT 1
)
ORDER BY `key_part2` DESC
LIMIT 1

EXPLAIN , , , fileort. , ​​ , , , 3 .

  • ? , , , , - . , , ?
  • , MySQL , , , MySQL?

, MySQL 5.6.22.

:

"" " ". , , LIMIT 1 LIMIT 2 - , . , LIMIT 1.

+4
1

, MySQL "", , . MySQL , ( , ..).

:

SELECT * FROM t1 key_part1= (   SELECT key_part1  FROM t1  ORDER BY key_part1 ASC    1 ) key_part2 DESC LIMIT 1

key_part2, key_part1. , mysql ; ORDER BY key_part1 DESC, key_part2 DESC. ORDER BY , .

, :

SELECT * FROM t1 WHERE key_part1= # { } key_part2 DESC LIMIT 1

#{some value} . , , key_part1. , key_part1.

0

More articles:


All Articles