Clever Geek Handbook

MySQL MAX_JOIN_SIZE error! The need to optimize the query

I am running 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 have over 42,000 lines underyc_zipcodes

. Other tables currently have less than 10 rows.

EDIT: Sample data 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   xxxxxx@gmail.com   92831  1
2          jason       c2d0d212936c4bfd7f587607e6c72808   jason    stevenson  xxxxxx@gmail.com   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. There were no ridiculous bears at the time of creating this app = D

+2


source to share


4 answers


You should be using JOIN and not just select from all tables. If you select from all tables, all possible combinations of rows are generated (and that's a LOT) and then 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 don't see any obvious problems with your request. I would just set the options when the error message tells you, then see if the result is what you want. If that's good. If not, come back and tell us.

+4


source


You should strive to reduce the size of the result set - make sure the connection will filter the records needed earlier; more careful selection of indexes should help with this.

  • Do you have indexes (at least) on yc_users.id or yc_news.id and yc_zipcodes.zip?
  • Try to run the query without ORDER BY and see if it matters

See also related StackOverflow threads:



More about MAX_JOIN_SIZE and SQL_BIG_SELECTS from the official MYSQL documentation.

+2


source


The WHERE clause is equivalent to INNER JOINs (although I clearly prefer JOINs) and seems to be wired correctly and does not result in an unintended CROSS JOIN.

What version of MySQL are you using?

0


source


While the query might be slightly optimized (depending on what you are trying to accomplish), I don't see how it can be changed to a point that will not result in an error when presenting information. As the accepted answer here says , you probably agree with joins and set SQL_BIG_SELECTS = 1.

That said, I would appreciate the need for the joins you are creating and probably opened up another question with more information about what exactly you are trying to accomplish with the data you are requesting.

At first glance, I can assume that you are grabbing all the articles created by the user and where they come from. If so, I would post the news to a LEFT JOIN and get the zip relationship in another request.

0


source






More articles:


All Articles