MySQL Order by Optimization

Question

Below is the structure of a table:-

Article: ID, Title, Desc, PublishedDateTime, ViewsCount, Published

Primary Key: ID

Query Used:

Select Title FROM Article ORDER By ViewsCount DESC, PublishedDateTime ASC

As you can see that I am mixing ASC and DESC & according to MySQL Order By optimization, indexes will not be used.

I have thought to use a composite index using the ViewsCount and PublishedDateTime. Do you recommend to use 2 different keys instead of using composite index. But then I have read that composite index is better than using 2 different keys (if both fields are going to be used).

Some more information shared:

The table contains more than 550K+ records and also I am having big trouble in adding and deleting indexes for test purpose. What do you guys recommend ? Should I test on a small sample ?

Below are some more insights:

Indexes Used:
1) ViewsCount
2) PublishedDateTime.
3) ViewsCount & PublishedDateTime (named as ViewsDate_Index )

A) EXPLAIN Query mixing ASC and DESC:

EXPLAIN SELECT title FROM  `article` ORDER BY ViewsCount DESC , PublishedDateTime ASC  LIMIT 0 , 20    

====+===============+=========+======+===============+=====+=========+======+========+================+
id  | select_type   | table   | type | possible_keys | key | key_len | ref  | rows   | Extra
1   | SIMPLE        | article | ALL  | NULL          | NULL| NULL    | NULL | 550116 |  Using filesort
====+===============+=========+======+===============+=====+=========+======+========+================+

B) EXPLAIN Query using the same sorting order:

EXPLAIN SELECT title FROM  `article` ORDER BY ViewsCount DESC , PublishedDateTime DESC  LIMIT 0 , 20

====+===============+=========+=======+===============+=================+=========+=============+========+================+
id  | select_type   | table   | type  | possible_keys | key             | key_len | ref         | rows   | Extra
1   | SIMPLE        | article | index | NULL          | ViewsDate_Index | 16      | NULL        | 550116 |  
====+===============+=========+=======+===============+=================+=========+=============+========+================+

You can see that if ViewsCount and PublishedDateTime are in 2 same sorting order then it uses the ViewsDate_Index index. One thing that I found strange was that possible_keys is NULL and still it selects an index. Can someone explain the reason for this.

Also any tips on adding indexes on this table because it takes alot of time to add a new index. Any workaround or help in this regarding will be appreciated.

Answer 1

ORDER By ViewsCount DESC, PublishedDateTime ASC

is handled effeciently by MySQL 8.0 if you have

INDEX(ViewsCount DESC, PublishedDateTime ASC)

8.0 is when MySQL started honoring the ASC/DESC in INDEX specifications.

Answer 2

First of all, run the whole query live, and see how it performs. When you've got some benchmarks down, plug the query into your MySQL console and prepend EXPLAIN to it. MySQL will not perform the query, but it will display it's plan to execute the query, including where it thinks is important to optimize, which indexes it will use, how many rows it has to traverse, and how efficiently it will traverse each set of rows, among other things as well. The best way to gauge a performance problem is through benchmarking. Use it often.

Answer 3

In practice, indexes won't be used even for ORDER By ViewsCount, PublishedDateTime here, since you SELECT all columns and apply no condition. Is it a real query? Because any conditions will spoil your optimizations.

If your table is so small that you are going to pull it as the whole, indexes will only slow down your query. (Relates to the original query: SELECT * FROM article ORDER BY ViewsCount DESC, PublishedDateTime;)

UPD

In case where you have 500K+ rows, I think you are going to use LIMIT clause. I would do the following:

1. add an index on (ViewCount, PublishedDateTime)

2. rewrite the query as follows:

   SELECT Title
   FROM (
       SELECT id
       FROM article
       ORDER BY ViewsCount DESC, PublishedDateTime
       LIMIT 100, 100
   ) ids
   JOIN article
   USING (id);
   

The ordering would benefit from operating on a subset of data from the covering index. The join will just obtain Titles by ids.

UPD2

Another query that might work much better when the cardinality of the ViewCount is rather small (though you should benchmark):

SELECT Title
FROM (
  SELECT ViewCount
  FROM article
  GROUP BY ViewCount DESC) as groups
JOIN article USING (ViewCount)
LIMIT 0, 100;

It as well assumes you have (ViewCount, PublishedDateTime) index on the table.