MySQL MAX_JOIN_SIZE Error! Need to optimize query.

Question

I run this query:

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

And get this error:

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

I've got over 42,000 rows under yc_zipcodes. The other tables hold less than 10 rows at the moment.

EDIT: Data samples as requested:

yc_zipcodes

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   [email protected]  92831 1
2          jason    c2d0d212936c4bfd7f587607e6c72808   jason    stevenson  [email protected]   93710 1

yc_news

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

The data above is dud. No gummy bears were during the making of this application =D

Answer 1

You should use a JOIN, and not just select from all tables. If you select from all tables, all possible combinations of rows are generated (and this are A LOT) and then the WHERE filters out unneeded rows.

Use this, for example:

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

EDIT:

I can't see any obvious problems in your query. I would just set the options as the error message tells you and then look if the result is the one you wanted to get. If it is - fine. If it isn't - come back and tell us.

Answer 2

Your WHERE clause is equivalent to INNER JOINs (although I generally prefer explicit JOINs) and appears to join properly and not result in an inadvertant CROSS JOIN.

What version of MySQL are you using?

Answer 3

You should aim to reduce the size of the result set - ensure the join(s) will filter records needed sooner; choosing indexes more carefully should help with this.

• Do you have indexes (at least) on either yc_users.id or yc_news.id and yc_zipcodes.zip?
• Try running the query without the ORDER BY and see if it makes a difference

See also these related StackOverflow threads:

• MySQL MAX_JOIN_SIZE errors
• MySQL - SQL_BIG_SELECTS
• MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”

More details about max_join_size

Answer 4

While the query could be a little more optimized (depending on what you are trying to accomplish), i do not see how it can be changed to a point which would not trigger the error as the information is presented. As the accepted answer here states, you are probably ok with the joins and setting SQL_BIG_SELECTS=1.

However, that being said, I would evaluate the necessity of the joins you are making and probably open another question with more information on exactly what you are trying to accomplish with the data you are querying.

From first glance I can assume that you are grabbing all articles a user made and where they are from. If this is the case, I would LEFT JOIN news to user and get the zip relationship in another query.