为什么这句explain出来的type是all
explain SELECT u.id FROM user AS u, user_friend AS uf WHERE
(uf.user_id = 1 AND uf.friend_id = u.id) OR
(uf.friend_id = 1 AND uf.user_id = u.id) ;
改成select xx union select xx就快很多
explain select xx union select xx 这个呢
或者你再执行遍
SELECT u.id FROM user AS u, user_friend AS uf WHERE
(uf.user_id = 1 AND uf.friend_id = u.id) OR
(uf.friend_id = 1 AND uf.user_id = u.id) ;
SELECT u.id FROM user AS u, user_friend AS uf WHERE
(uf.user_id = 1 AND uf.friend_id = u.id)
不带or的看看走得什么explain
根据“Using union(PRIMARY,user_friend_friend_ref);”
user_friend.user_id是主键??!!
-
SQL code
SELECT u.id FROM user u INNER JOIN user_friend uf ON u.id = uf.user_id WHERE uf.user_id = 1 OR uf.friend_id = 1;
因为OR的原故。
这样导致MYSQL无法使用索引,如果只是一张表,MYSQL还会使用索引合并技术来优化。但你现在是两张表,导致MYSQL无法常规优化。 当然也可以说是MYSQL做的不理想的地方。