CODEWITHSUNDEEP
mysqlquery-optimizationmin
drew
drew

Reputation: 11

mysql select min performance issues

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";

Upvotes: 1

Views: 811

Answers (2)

Sten-&#197;ke
Sten-&#197;ke

Reputation: 45

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).

Upvotes: 0

Jonas Kongslund
Jonas Kongslund

Reputation: 5268

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.

Upvotes: 1

Related Questions