I have three tables in MySQL - gallery, gallery_likes and gallery_info.

I want to obtain photos from gallery based on the folowing query:

SELECT g.photo_id, g.name
FROM gallery g 
LEFT JOIN gallery_likes AS gl ON gl.info_id = g.info_id
LEFT JOIN gallery_info AS gi ON gi.id = g.info_id
WHERE gl.user_id = 456 AND gl.photo_id < 113535 
ORDER BY gi.insert_date DESC 

However, ORDER BY in this setup is very slow. Query takes 5s. If I use ORDER BY g.info_id instead, it takes 200ms.

I have index on id, info_id, photo_id and insert_date columns

1 Answer 1


You need the next indexes:

gallery:       info_id
gallery_likes: (info_id, user_id, photo_id)
gallery_info:  (id, insert_date)
  • I have added index to user_id (that one I missed), but the result is nearly the same. Commented Apr 14, 2019 at 11:29
  • @MartinPerry You need a milticolumn indexes if multiple columns are used in the same query.
    – Kondybas
    Commented Apr 14, 2019 at 11:30
  • Oh, yes, I see.. helped. Can you please explain why? Commented Apr 14, 2019 at 11:44
  • 1
    Indexes are used for search. Mysql can utilize only one index per table in the query. Single column index is suitable for search by single criteria. If you looking for the rows that satisfy a number of conditions, you need an index that cover all the columns searched. Otherwise the search will be partially covered by a single column index while all the other searches will be performed by table scan.
    – Kondybas
    Commented Apr 14, 2019 at 11:57
  • Oh, I see... thank you for the explanation. Commented Apr 14, 2019 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.