Optimize a MySQL request that take long time to load with an ORDER BY

Question

I'm facing an issue of performance with a MySQL query, even with indexing the column needed, etc... It take more than 5 seconds to load the 10 firsts results and it's due to the ORDER BY, when I remove it, it take only few milliseconds to load!

Here is the SQL query:

select
    *
from
    `feedbacks` f
inner join carriers c on c.id = f.carrier_id 
where
    c.id in (25619, 25620, 25621, 25637, 25758, 25759, 25760, 25761, 25762, 25763, 25976, 25983, 26459, 26460, 27003, 27006, 27052, 27295, 27325, 27387, 27532, 27533, 27534, 27535, 27536, 27537, 27538, 27541, 27542, 27543)
    and f.`deleted_at` is null
order by
    f.`created_at` desc
limit 10 offset 0

There is some index on the feedbacks table:

feedbacks_status_id_foreign BTREE   FALSE   status_id
feedbacks_push_id_foreign   BTREE   FALSE   push_id
feedbacks_created_at_index  BTREE   FALSE   created_at
feedbacks_carrier_id_created_at_token_index BTREE   FALSE   carrier_id,created_at,token
feedback_test_index BTREE   FALSE   created_at,carrier_id
PRIMARY BTREE   TRUE    id

And inside the carriers table:

idx_creator_id  BTREE   FALSE   creator_id
carriers_team_id_foreign    BTREE   FALSE   team_id
carriers_slug_unique    BTREE   TRUE    slug
carriers_slug_organization_id_created_at_index  BTREE   FALSE   slug,created_at
PRIMARY BTREE   TRUE    id

But even with all of these indexes, it take a long time!

More help are welcome for this issue :D

Answer 1

The trick is to find ids of the rows in questions, then fetch the extra columns. This avoids hauling around lots of junk, through sorts, etc, and may allow for using "covering" indexes:

select  f2.*, c.*
    FROM  (
        SELECT  f.id
            FROM  `feedbacks` f
            WHERE  f.carrier_id in (25619, 25620, 25621, 25637, 25758,
                        25759, 25760, 25761, 25762, 25763, 25976, 25983, 26459,
                        26460, 27003, 27006, 27052, 27295, 27325, 27387, 27532,
                        27533, 27534, 27535, 27536, 27537, 27538, 27541, 27542,
                        27543 
                          )
              and  f.`deleted_at` is null
            order by  f.`created_at` desc
            limit  10 offset 0 
          ) AS f1
    JOIN  carriers AS c  ON c.id = f1.carrier_id
    JOIN  feedbacks AS f2  ON f2.id = f1.id
    ORDER BY  f2.`created_at` desc  -- yes, repeated
    ;    

with:

feedbacks:  INDEX(deleted_at, carrier_id, created_at,  id)