MySQL order optimization

Question

Below is the table structure: -

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

Primary Key: Identifier

Used query:

Select Title FROM Article ORDER By ViewsCount DESC, PublishedDateTime ASC 

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

I thought of using a composite index using ViewsCount and PublishedDateTime. You recommend using 2 different keys instead of using a composite index. But then I read that a composite index is better than using two different keys (if both fields will be used).

Additional Information:

The table contains more than 550K + records, and there are also big problems with adding and removing indexes for testing purposes. What do you guys recommend? Should I test a small sample?

The following are some additional information:

Indexes Used:
1) ViewsCount
2) PublishedDateTime.
3) ViewsCount and PublishedDateTime (called ViewsDate_Index)

A) EXPLAIN Mixing ASC and DESC queries:

 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 sort 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 PublisherDateTime are in the same sort order, then it uses the ViewsDate_Index index. The only thing that seemed strange to me was that possible_keys is NULL, and yet it selects the index. Can someone explain the reason for this.

Also any tips for adding indexes to this table, because adding a new index takes a lot of time. Any workaround or help in this regard would be appreciated.

Answer 1

First of all, run the entire query live and see how it runs. When you have some tests, connect the query to the MySQL console and add EXPLAIN to it. MySQL will not execute the query, but it will show that it plans to execute the query, including where it considers it important to optimize which indexes it will use, how many rows it should go through and how efficiently it will go through each set of rows . The best way to evaluate a performance problem is benchmarking. Use it often.

Answer 2

In practice, indexes will not even be used for ORDER By ViewsCount, PublishedDateTime here, since you select all columns and do not apply any conditions. Is this a real request? Because any conditions will ruin your optimizations.

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

UPD

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

• add index to (ViewCount, PublishedDateTime)

• rewrite the request as follows:

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

An order will be useful for working with a subset of the data from the coverage index. The connection will simply receive captions by identifiers.

UPD2

Another query that may work much better when the power of ViewCount is pretty low (although you should be guided):

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

He also assumes that the table has an index (ViewCount, PublishedDateTime).

Answer 3

Go ahead, use indexes in your database.