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;
这句报错，不知道原因在哪？
还有其他什么方面可以优化的吗？
explain呢？table desc呢？
补充一下：通常数据量大，检索条件复杂，请求量也大的应用场景，考虑一下sphinx、elasticsearch的解决方案。
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 后面的字段
=========================================================================================
我暂时想到这些,欢迎交流。