mysql 多条件join_MySQL多条件join优化

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;

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

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