mysql select min performance issues

Question

I have this query to select the minimum ID. It seems to work fine in the first few clicks, but it gets really sluggish when I keep on clicking the button that calls this query.

any workarounds for this query?

"select min(t1.blog_id) as min_id 
 FROM 
       (SELECT blog_id FROM myblogs_view 
        where blog_id<'$id' ORDER BY blog_id DESC LIMIT 10) as t1";

What does "Keep on clicking" mean? Is this query executed _exactly_ the same each time, or does something change? — Richard H, Apr 30 '11 at 10:00
it's the query for my next page to get the minimum id. so every time i click the next page button, this query executes. — drew, Apr 30 '11 at 10:04

score 1 · Answer 1 · answered Apr 30 '11 at 10:04

Try with

SELECT MIN(blog_id) AS min_id
FROM myblogs_view
WHERE blog_id < '$id'
GROUP BY blog_id

Do you have an index on the blog_id column?

In general when you are dealing with a slow query, you can use the EXPLAIN command to see how MySQL executes the query.

score 0 · Answer 2 · edited May 23 '17 at 10:28

There is one thing i would like to add: If you use your query for pagination, you should take a look at How can I speed up a MySQL query with a large offset in the LIMIT clause?

You might ask why is it relevant? You describe that your query runs sluggishly after you have clicked on the next page when you keep on clicking. This is likely when you have reached a large offset. My guess is that you use your query to calculate an offset, which you then use to fetch the relevant records with an limit offset query. Is that correct? If so, then your other query might benefit from the link i provided (i'm not the author of that answer, give credit where credit is due).

mysql select min performance issues

2 Answers2