优化长mysql查询

Question

我有一个多年前建立的聊天网站的查询，由于大量的流量，我糟糕的查询设计已经赶上了我。下面是我的长查询日志中的一个示例：

SELECT  DISTINCT user.id 
FROM    user 
        STRAIGHT_JOIN user_pics 
            ON user.id=user_pics.uid      
        STRAIGHT_JOIN user_account 
            ON user_account.user_id=user.id 
WHERE   registered = 1  AND 
        user.id<>0 AND 
        user.id<>23847 AND 
        user.id<>12392... (IT HAS LITERALLY 1000 OF THESE)
        AND user_pics.main=1 AND 
        user_pics.approved=1 AND 
        user_pics.deleted<>1 AND 
        gender LIKE '%female%' AND 
        country LIKE '%United Kingdom%' AND 
        city LIKE '%birmingham%' AND 
        sexorientation     LIKE '%Straight%' 
ORDER   BY updatedate DESC 
LIMIT   20;

查询大约需要15秒才能执行，我也已经为引用的所有列建立了索引。用临时表中的查找替换1000“和user.id<>0”标记会提高查询效率吗？我想我应该在去做改变之前问一下。如果您能推荐任何对代码有帮助的更改，我将不胜感激。

编辑:在php中通过一个简单的select和一个foreach数组循环来创建"user.id<>23847“标记，然后将它们添加到更大的sql查询中。

编辑2:感谢所有的帮助，通过使用“不在”他们的查询从13秒减少到0.3秒。

Answer 1

是的，如果你要用，

user.id NOT IN (SELECT id FROM idExemptTable)

这将比单独检查每个id快得多

我已经包含了一个指向另一个答案的链接，该答案进一步详细介绍了IN语句：

SQL: SELECT IN faster and best practice?

我也不确定为什么你要使用通配符匹配和喜欢检查国家名称。

Answer 2

尝试使用EXPLAIN

http://dev.mysql.com/doc/refman/5.0/en/explain.html

你将会看到什么可以更好。

可以使用NOT IN()将其替换为user.id NOT IN(23847 ,0 , 23847 ,...)

Answer 3

向users表中添加一个额外的字段，并对其进行索引。为中的每个用户将该值设置为1

user.id<>0 AND 
user.id<>23847 AND 
user.id<>12392...

对于每个其他用户，则设置为0。

然后在上面的查询中按此字段进行过滤。