mysql UNION vs IN clause

Question

I have been trying to optimize one of my queries which looks like:

select toc.* from
    (select sender, max(convid) as maxconvid
     from MetaTable 
     where sender in ('arjunchow','aamir_alam')
     group by sender) as tmp1
    inner join MetaTable as toc on 
        toc.sender = tmp1.sender 
        and toc.convid = tmp1.maxconvid;

When the mysql server is under stress, this query normally responds within 0.2 sec, but when the number of "sender" ids in the IN clause increase (>50), the query slows down terribly (~5-6 sec).

Is it advisable to use multiple union clauses instead of an IN clause given that my query may become a union of 50 queries. So my query will look like :

(SELECT 
    convId, 
    UNIX_TIMESTAMP(timeStamp) as timeStamp, 
    UNIX_TIMESTAMP(createTime) as createTime, 
    numMessages, 
    rootMsg, 
    sender, 
    ipormobile, 
    modIpOrMobile, 
    UNIX_TIMESTAMP(modTimeStamp) as modTimeStamp 
 from 
    MetaTable 
 where 
    sender='arjunchow' 
 ORDER BY convId DESC limit 1) 
UNION ALL 
(SELECT 
    convId, 
    UNIX_TIMESTAMP(timeStamp) as timeStamp, 
    UNIX_TIMESTAMP(createTime) as createTime, 
    numMessages, 
    rootMsg, 
    sender, 
    ipormobile, 
    modIpOrMobile, 
    UNIX_TIMESTAMP(modTimeStamp) as modTimeStamp 
 from 
    MetaTable 
 where 
    sender='aamir_alam' 
 ORDER BY convId DESC limit 1)

Answer 1

Rewrite your query as following:

SELECT  *
FROM    MetaTable
WHERE   (sender, convid) IN
        (
        SELECT  sender, MAX(convid) as maxconvid
        FROM    MetaTable
        WHERE   sender IN ('arjunchow','aamir_alam')
        GROUP BY
                sender
        )

, make sure you have a composite index on (sender, convid) and make sure it's used (i. e. there is USING INDEX FOR GROUP BY in the explain plan)

Answer 2

I had a similar issue with tag searching and actually had the opportunity to talk to MySQL techs about it; they recommended that I create a temporary table and add my values to that as you loop through the users then perform any operations you need to on that temp table. Any time you try to pack this much into a single command, it's going to get seriously bogged down.