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


You need the next indexes:

gallery:       info_id
gallery_likes: (info_id, user_id, photo_id)
gallery_info:  (id, insert_date)
| improve this answer | |
  • I have added index to user_id (that one I missed), but the result is nearly the same. – Martin Perry Apr 14 '19 at 11:29
  • @MartinPerry You need a milticolumn indexes if multiple columns are used in the same query. – Kondybas Apr 14 '19 at 11:30
  • Oh, yes, I see.. helped. Can you please explain why? – Martin Perry Apr 14 '19 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 Apr 14 '19 at 11:57
  • Oh, I see... thank you for the explanation. – Martin Perry Apr 14 '19 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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