CODEWITHSUNDEEP
mysql
Judking
Judking

Reputation: 6381

How to optimize this mysql query with where, order by as well as pagination?

The query is as follows:

select * from table 
where category in ('A', 'B') 
order by upload_time desc 
limit 60, 20;

where category is of type varchar(60), and upload_time is datetime.

Could anyone tell me where to create index is most suitable for this scenario? Thanks a lot.

Upvotes: 0

Views: 35

Answers (1)

D Mac
D Mac

Reputation: 3809

You can find out how the query optimizer regards your query by using "EXPLAIN SELECT ...", but I'll admit the output can be hard to understand for a beginner.

You can put an index on category because you're using that in a WHERE clause. You can also put an index on upload_time because you are sorting on it.

Finally, you can use something other than "select *" because that will bring back all the columns in the table and if you aren't using them all you're doing more I/O than you need.

Upvotes: 1

Related Questions