[Solved] MySQL: ORDER BY significantly slows the query

EverSQL Database Performance Knowledge Base

MySQL: ORDER BY significantly slows the query

Database type:

Please consider a table with queue_name, priority and message_timestamp columns.

I'm going to perform the following query:

SELECT message_timestamp 
  from queue_messages 
 WHERE queue_name = 'name' 
   AND state = 0 
 ORDER 
    BY message_timestamp DESC 
 LIMIT 1

Here is a compound index for that:

CREATE INDEX STATE_QUEUENAME_TIMESTAMP ON `queue_messages` (queue_name, state, message_timestamp);

enter image description here

EXPLAIN shows that index matches the query pretty good (there is no filesort for ORDER BY): enter image description here

My problem is that without ORDER BY message_timestamp I have throughput ~200 prs for this query, but with it ~50 rps!

And more rows in the table, slower the query with ORDER BY!

What am I doing wrong?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `queue_messages` ADD INDEX `queue_messages_idx_queue_name_state_message_ti` (`queue_name`,`state`,`message_timestamp`);
ALTER TABLE `queue_messages` ADD INDEX `queue_messages_idx_message_timestamp` (`message_timestamp`);
The optimized query:
SELECT
        queue_messages.message_timestamp 
    FROM
        queue_messages 
    WHERE
        queue_messages.queue_name = 'name' 
        AND queue_messages.state = 0 
    ORDER BY
        queue_messages.message_timestamp DESC LIMIT 1

Related Articles



* original question posted on StackOverflow here.