MySQL MAX_JOIN_SIZE 错误!需要优化查询

Question

我运行此查询:

SELECT u.user_id, u.fname, u.lname, n.title, n.news_id, n.post, 
n.zip, z.city,z.state_abbr
FROM yc_users u, yc_news n, yc_zipcodes z
WHERE u.user_id = n.user_id AND n.zip = z.zip
ORDER BY n.stamp
LIMIT 10

并收到此错误:

The SELECT would examine more than MAX_JOIN_SIZE rows; 
check your WHERE and use SET SQL_BIG_SELECTS=1 or 
SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

yc_zipcodes 下有超过 42,000 行。其他表目前少于 10 行。

编辑:根据要求的数据样本:

yc_邮政编码

zip   city        state_abbr
00210   Portsmouth  NH
00211   Portsmouth  NH
00212   Portsmouth  NH
00213   Portsmouth  NH

yc_users

user_id  username    password                           fname    lname      email              zip   active_bln
1          fission1    e09dc84a23fd6cd68ce1fff1ff95713a   Hayden   Ferguson   <a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="5e2626262626261e39333f3732703d3133" rel="noreferrer noopener nofollow">[email protected]</a>   92831  1
2          jason       c2d0d212936c4bfd7f587607e6c72808   jason    stevenson  <a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e9919191919191a98e84888085c78a8684" rel="noreferrer noopener nofollow">[email protected]</a>   93710  1

yc_新闻

news_id user_id   title                         post                                            zip    stamp     active_bln
2      1         Gummy bear falls into manhole    OMG! A drunk man dressed as gummy  bear...      93740 2009-10-12 09:49:04 1
3      1         Guy robbed                       Some dude got robbed last night at corner of... 93740 2009-10-12 09:50:19 1

上面的数据是无效的。制作此应用程序期间没有小熊软糖 =D

Answer 1

您应该使用 JOIN，而不仅仅是从所有表中进行选择。如果您从所有表中进行选择，则会生成所有可能的行组合(而且数量很多)，然后 WHERE 会过滤掉不需要的行。

使用这个，例如:

SELECT       u.user_id, 
             u.fname, 
             u.lname, 
             n.title, 
             n.news_id, 
             n.post, 
             n.zip, 
             z.city,
             z.state_abbr
FROM         yc_users u
INNER JOIN   yc_news n 
ON           u.user_id = n.user_id
INNER JOIN   yc_zipcodes z
ON           n.zip = z.zip
ORDER BY     n.stamp
LIMIT        10

编辑:

我在您的查询中看不到任何明显的问题。我只需按照错误消息告诉您的方式设置选项，然后查看结果是否是您想要的结果。如果是的话——很好。如果不是 - 请回来告诉我们。