sql - mysql UNION 与 IN 子句

Question

我一直在尝试优化我的一个查询，如下所示:

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;

当 mysql 服务器承受压力时，此查询通常会在 0.2 秒内响应，但是当 IN 子句中的“发件人”id 数量增加(>50)时，查询速度会非常慢(~5-6 秒) .

鉴于我的查询可能成为 50 个查询的联合，是否建议使用多个联合子句而不是 IN 子句。所以我的查询看起来像:

(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

我在标签搜索方面遇到了类似的问题，实际上有机会与 MySQL 技术人员讨论这个问题；他们建议我创建一个临时表并在循环访问用户时将我的值添加到该表中，然后在该临时表上执行您需要的任何操作。任何时候你试图将这么多打包到一个命令中，它都会陷入严重的困境。