CODEWITHSUNDEEP
mysql
HyderA
HyderA

Reputation: 21401

MySQL MAX_JOIN_SIZE Error! Need to optimize query

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

Upvotes: 2

Views: 2580

Answers (4)

Adrian
Adrian

Reputation: 6101

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:

More details about MAX_JOIN_SIZE & SQL_BIG_SELECTS from official MYSQL documentation.

Upvotes: 2

Kevin Peno
Kevin Peno

Reputation: 9196

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.

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89721

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?

Upvotes: 0

Maximilian Mayerl
Maximilian Mayerl

Reputation: 11357

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.

Upvotes: 4

Related Questions