MySQL Query Needs Optimizations

Question

I have this users table with:

id : int (255)
name: char (100)
last_comment_target: int(100)
last_comment_date: datetime

This table has around 1.3mil rows.

PKEY and BTREE is on id, last_comment_target, and last_comment_date.

And, I am trying to perform a range query:

SELECT * FROM users 
WHERE id IN (1,2,3,5,...[around 5000 ids]) 
AND last_comment_target > 0 
ORDER BY last_comment_dt DESC LIMIT 0,20;

Sometimes the query can take as long as 3 seconds. I wonder if there are better ways to optimize this query. Or, if this query can be rewritten.

Thank you so much for your help.

Where are the 5000 ids coming from? If you can put them into a table (or of they're already in a table) and do a join, it may be faster. Even better, if the 5,000 ids can be associated to an attribute in users, you could just specify that as simple condition - and even index it, if necessary. — Doug Kress
– Doug Kress, Commented Aug 19, 2011 at 19:33
@Heru Setiawan: No, int(255) is only a display width hint. It does not set the size of the integer. See stackoverflow.com/questions/7073152/… — Bill Karwin
– Bill Karwin, Commented Aug 19, 2011 at 19:46

Karolis · Accepted Answer · 2011-08-19 19:46:20Z

1

SELECT u.* 
FROM 
    users u
    JOIN (
        SELECT 1 id 
        UNION ALL
        SELECT 2 id
        UNION ALL
        :
        :
        SELECT 5000 id
    ) ids ON ids.id = u.id
WHERE 
    last_comment_target > 0 
ORDER BY 
    last_comment_dt DESC 
LIMIT 0, 20;

answered Aug 19, 2011 at 19:46

Karolis

9,59231 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Heru S Over a year ago

Thank you. I wonder if you can help me shed some light on your answer. Why are we using the SELECT 1 id UNION ALL SELECT 2 id UNION ALL .... as part of the JOIN? Can we actually do a JOIN on the table where we derived from the other table? What difference will it make (if any)?

Karolis Over a year ago

@Heru This creates a temporary table of all ids. Joining tables usually is a faster solution, but it depends on many factors and you should test it. If you have all ids in some other table then you can use your other table instead of these UNIONS: select id from other_table

Heru S · Accepted Answer · 2011-08-20 05:38:13Z

Thanks everyone that has contributed.

@Karolis seems to point out that an alternative using join instead of range

So, basically:

SELECT * FROM users WHERE id IN (1,2,3,...[5000 ids]) AND last_comment_target > 0

yields in EXPLAIN statement a type of RANGE. The 5000 ids can be generated from another table.

When I switched the above to:

SELECT *
FROM users u
INNER JOIN user_friends uf ON u.id = uf.to_id
AND u.last_comment_target > 0
AND uf.from_id = [id];

It yields in EXPLAIN statement two types: ref and eq_ref which is faster than range in this query.

The query execution is reduced from 3+ seconds to around 0.2x seconds.

So, lesson learned from my end: TRY to use JOIN instead of RANGE if you have a table that you can derive from.

Collectives™ on Stack Overflow

MySQL Query Needs Optimizations

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related