SELECT s.id AS num FROM player p,php_session s WHERE inmap=5 and p.id = s.id表结构
player 用户表( id 主键 inmap 索引)
php_session 现在表 (id 主键 == player.id 用户id的外键)修改SQL:
1.SELECT s.id AS num FROM player p LEFT JOIN php_session s ON p.id = s.id
WHERE inmap=5 这样会查出所有inmap=5 但是 并不在php_session 中。即有一部分S.ID == null2.SELECT s.id AS num FROM player p LEFT JOIN php_session s ON p.id = s.id
WHERE inmap=5 AND s.id IS NOT NULL 跟没有优化的SQL一样EXPLAIN SELECT s.id AS num FROM player p,php_session s WHERE inmap=5 and p.id = s.id
结果如下
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE s ALL PRIMARY NULL NULL NULL 2
1 SIMPLE p eq_ref PRIMARY,inmap PRIMARY 4 pm80.s.id 1 Using where
FROM session s
LEFT JOIN player p on s.id = p.id and p.inmap = 5
WHERE s.id IS NOT NULL