Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite 
  # I have a simple table
  create table t1 (c1 int, c2 int);
  # I want to do the following sorting
  select * from t1 order by c2 desc, c1 desc;
  select * from t1 order by c2 asc, c1 desc;
  # add indexs
  CREATE INDEX index_1 ON t1 (c1 DESC, c2 DESC);  
  CREATE INDEX index_2 ON t1 (c1 ASC, c2 DESC);  
  # then explain
  explain select * from t1 order by c2 desc, c1 desc;
  # the result is
  mysql> explain select * from t1 order by c2 desc, c1 asc;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | index | NULL          | index_2 | 10      | NULL |    6 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

My questions are

  1. Are the indexs optimized the performance?
  2. Why Using index; Using filesort show at the same time?
share|improve this question
add comment (requires an account with 50 reputation)

1 Answer

up vote 1 down vote accepted

You need an index on (c2, c1) for this query, not an index on (c1, c2).

Of course it would be best if there was an index (c2 DESC, c1 DESC) for the first and an index (c2 DESC, c1 ASC) for the second query. Unfortunately MySQL cannot create such indexes. The ASC and DESC are ignored.

Which means your two indexes:

CREATE INDEX index_1 ON t1 (c1 DESC, c2 DESC);  
CREATE INDEX index_2 ON t1 (c1 ASC, c2 DESC);

are actually identical and same as if you had defined them as:

 CREATE INDEX c1_c2_index ON t1 (c1 ASC, c2 ASC);

If the performance - with the (c2, c1) index - is not good enough, you have a few options:

  • Since the columns are of integer type, add two more columns and store the negative values. You'll need triggers to polulate those columns.

. 

CREATE TABLE 
( c1 INT
, c2 INT
, c1_neg INT
, c2_neg INT
);

Then you can an indexes on (c2_neg, c1) and (c2_neg, c1_neg)

  • If you can move to MariaDB, you can use VIRTUAL PERSISENT columns and appropriate indexes for a similar solution (a bit better as you won't have to add triggers, the columns will be populated automatically).
share|improve this answer
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.