The official documentation of mysql descending indexes says a bit about performance , so I was curious about how much performance will it change if the index is used in a right way ASC or DESC.
https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html
Part1
・mysql --version
mysql Ver 8.0.21 for osx10.13 on x86_64 (Homebrew)
Let's go throw some basics first,
While adding an Indexes on a table, by default the order BY is 'ASC'
let's see the table details for reference;
・mysql> DESCRIBE chair1;
let's check the indexes on our table first and add and drop indexes later as per this documentations check,
mysql> SHOW INDEXES FROM chair1;
Now I will add a popularity index, let's check the collation afterwards
→ Alter table chair1 add key (popularity); ←by default it will be an index by ASC
let's check by executing a simple query
・ mysql> select * from chair1 order by popularity;
execution result:
・execute another query with order by DESC
mysql> select * from chair1 order by popularity DESC;
execution result:
In this cases, there is not that much performance difference in both queries; but depending upon the query may be the performance difference can be slightly high.
let's make the index DESC then execute the above queries;
・ mysql> Alter table chair1 add key (popularity DESC);
Now the collation is 'D' for popularity index,
・ mysql> select * from chair1 order by popularity; ← ASC by default
・mysql> select * from chair1 order by popularity DESC; ←changed order by same as the index DESC
A minor ignorable performance difference between both the queries.I feel like, for simple queries it doesn't change much but once the query become more complex with many condition and clauses then DESC index can be helpful that time.
Just to try I added some where clause on primary key and tried with order by ASC and DESC respectively.
↑Target rows 14414 in both cases with explain.
Part2:
Descending Indexes doesn't work with mysql version older then 8 ,so later on I will try the same desc and asc index query on mysql5.6, just for my own curiosity. I will update the execution result of query & performance on another post.
For reference I am going to follow the official link↓
https://dev.mysql.com/doc/refman/5.6/en/osx-installation-pkg.html
Thanks for reading till the end. Hoping for some positive comments & please correct me if anything wrong i followed.
Hopefully I will put part2 also soon!