MySQL优化 - 遍历整个表为WHERE foo IN子选择？

To learn MySQL (innodb) I'm attempting to write a twitter app with 3 tables: users2, follow2, and tweets2.

要学习MySQL(innodb)我正在尝试编写一个包含3个表的twitter应用程序:users2,follow2和tweets2。

Doing an explain as follows shows all the rows in tweets2 being possibly touched. Is this accurate? If you are going to do a WHERE username IN (select) I would assume that given username is indexed MySQL would retrieve the sub-select clause and go directly to the rows in tweets2 which have those usernames.

按如下所示进行说明,可能会触及tweets2中的所有行。这准确吗?如果你要做一个WHERE用户名IN(选择)我会假设给定的用户名是索引的,MySQL将检索sub-select子句并直接转到tweets2中具有这些用户名的行。

explain select username, timestamp, tweet
from tweets2
where username in 
  (select followee from follow2 where follower='user1') 
order by timestamp desc;

Heres the SQLAlchemy code creating the tables:

下面是创建表的SQLAlchemy代码:

    users = Table('users2', metadata,
    Column('username', String(16), index=True),
    Column('email', String(256)),
    )
    follow = Table('follow2', metadata,
    Column('follower', String(16), index=True),
    Column('followee', String(16), index=True),
   )
    tweets = Table('tweets2', metadata,
    Column('id', Integer, primary_key=True),
    Column('username', String(16), index=True),
    Column('timestamp', BigInteger),
    Column('tweet', String(200)),
    )

1 个解决方案

#1

In MySQL, the select statement inside the IN clause will always be driven (though the indexes may still be used on it).

在MySQL中,IN子句中的select语句将始终被驱动(尽管索引仍然可以在其上使用)。

Replace it with a JOIN:

用JOIN替换它:

SELECT  username, timestamp, tweet
FROM    follow2 f
JOIN    tweets2 t
ON      t.username = f.followee
WHERE   f.follower = 'user1'
ORDER BY
        timestamp DESC

#1