Optimizing MySQL Query Performance for 'ARCHIVED' Status Selection

Question

I have a table named "article" containing 1,145,141 records with various fields including

"id," "uiqPID," "tenant," "status," "title," "body," "user_id," "category_id," "created_at," and "updated_at." 

The "status" column can have one of three values: 'PUBLISHED,' 'DRAFT,' or 'ARCHIVED,' with the following counts:

• PUBLISHED: 2
• DRAFT: 26,145
• ARCHIVED: 1,118,993

I have set up the following indexes:

1. PRIMARY index on 'id'
2. article_abstract_unq_id index on 'uiqPID'
3. article_abstract_unq_id index on 'tenant'
4. article_status_idx index on 'status'
5. idx_composite_search index on 'id'
6. idx_composite_search index on 'uiqPID'
7. idx_composite_search index on 'created_at'

My issue is with the performance of the following query, which took 5.7 seconds to execute:

SELECT 
    a.id AS id,
    a.created_at AS created_at
FROM 
    article a
WHERE 
    a.status = 'ARCHIVED'
ORDER BY a.created_at DESC
LIMIT 50;

However, if I remove the WHERE condition or change it to a.status = 'DRAFT', the query completes within 1 second.

Upon inspecting the query plan, I noticed the difference in execution strategies. With the 'ARCHIVED' or 'DRAFT' status filter, the plan shows:

key: article_status_idx
Extra: Using index condition; Using filesort

But without the 'ARCHIVED' filter, the plan simply states:

key:
Extra: Using filesort

My question is: How can I optimize the query performance for filtering on 'ARCHIVED' status, ensuring it executes faster than the current 5.7 seconds, similar to the queries without this condition or with 'DRAFT' status?

Answer 1

You can create a composite index matching the query exactly:

create index idx on article (status, created_at desc);

Thus the DBMS can go to status = 'ARCHIVED' in the index, read the first 50 entries and be done with the task.

https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html