MySQL多条件join优化

Question

SELECT user.nickname, visitors.week_likes
FROM user
JOIN visitors ON ( user.id = visitors.user_id ) 
WHERE (
    user.role >=0
    AND visitors.week_num =10
    AND visitors.week_likes >0
    AND (
        user.show_index != -1
        OR user.show_index IS NULL
    )
)
ORDER BY visitors.week_likes DESC , user.last_seen DESC 
LIMIT 100

user.id和visitors.id是主键，user.id与visitors.user_id对应。对visitors.user_id建立了索引，没什么效果，在SQL语句和索引方面能怎样优化？

由于week_num和week_likes的区分度很低（<0.001）,所以我觉得建立索引没有太大的效果，但是，索引user_id换成（week_num，week_likes）后visitors的rows由9000+变为1700+了，至少行数来说还是很明显的（虽然查询时间上没有太大差别）

那先筛选user和visitor后再join会不会更好呢？

SELECT a.nickname, b.week_likes
FROM (SELECT * FROM user WHERE ( user.role >=0 AND (user.show_index != -1 OR user.show_index IS NULL)) a
JOIN 
(SELECT visitors.user_id, visitors.week_likes FROM visitors WHERE ( visitors.week_num = 10 AND visitors.week_likes >0)) b
ON ( a.id = b.user_id )
ORDER BY b.week_likes DESC , a.last_seen DESC 
LIMIT 100;

这句报错，不知道原因在哪？

还有其他什么方面可以优化的吗？

Answer 1

explain呢？table desc呢？

补充一下：通常数据量大，检索条件复杂，请求量也大的应用场景，考虑一下sphinx、elasticsearch的解决方案。

Answer 2

SELECT user.id,user.niclname,user.last_seen
FROM user
WHERE (
user.role >= 0
AND (
user.show_index != -1
OR user.show_index IS NULL
)
)
JOIN (
SELECT visitors.user_id,visitors.user_id,visitors.week_likes
FROM visitors
WHERE (
visitors.week_num =10
AND visitors.week_likes >0
)
)
ON ( user.id = visitors.user_id )
ORDER BY visitors.week_likes DESC , user.last_seen DESC
LIMIT 100

---

建立索引原则：
1.主键
2.外键
3.WHERE 后面的字段
4.ORDER BY、GROUP BY、DISTINCT 后面的字段

=========================================================================================
我暂时想到这些,欢迎交流。