1

Probably a simple SQL query, but struggling as still learning

The following query runs fine:

SELECT NationalArea. * 
FROM NationalArea
WHERE NationalArea.AreaCode =  '01922'

This returns about 30 results.

This also runs fine:

SELECT DestinationNames.Name
FROM `DestinationNames` 
WHERE DestinationNames.AreaCode = '01922'

This returns just the one

I am trying to run a query that joins the two where the National Area will give a list of area codes and the destination will match those area codes with the names of the towns. The query I have is as follows:

SELECT NationalArea.*, DestinationNames.Name
FROM NationalArea
JOIN DestinationNames
ON NationalArea.AreaCode=DestinationNames.AreaCode
WHERE NationalArea.AreaCode =  '01922'

But I get the following error

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

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mflammia
  • 87
  • 2
  • 9
  • possible duplicate of [MySQL MAX\_JOIN\_SIZE errors](http://stackoverflow.com/questions/966815/mysql-max-join-size-errors). The solution is also contained within the error message. – GarethD Jan 24 '13 at 12:53

1 Answers1

2

You can display the current value with

SHOW VARIABLES LIKE '%MAX_JOIN_SIZE%';

You can change it with:

SET MAX_JOIN_SIZE = 100

Or skip the check entirely with (run this as a separate command before your query):

SET SQL_BIG_SELECTS = 1

But I would first examine why your join returns more than that. It doesn't look like it should. The default value of max_join_size is 4294967295!

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Thanks for answering - I think its definitely my join but I can't see where. I am only expecting it to return 30 results as the where statement WHERE NationalArea.AreaCode = '01922' on the NationalArea table only returns 30 and I'm only joining the same Areacode numbers from the other table? – mflammia Jan 24 '13 at 13:01
  • The query in your question looks OK. I'd examine the data in the two tables? – Andomar Jan 24 '13 at 13:04
  • Not sure if its anymore help, but when I put SET SQL_BIG_SELECTS = 1 in front the query I now get: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT NationalArea.*, DestinationNames.Name FROM NationalArea JOIN Destinatio' at line 2. Its really odd, as the separate queries work fine but join them together and it fails - ill keep playing. Thanks. – mflammia Jan 24 '13 at 13:20
  • Are you putting `;` after `SET SQL_BIG_SELECTS = 1`? – GarethD Jan 24 '13 at 13:29
  • Nope, sorry, adding that interestingly now it works! and only gives the 30 results I was expecting. My SHOW VARIABLES LIKE '%MAX_JOIN_SIZE%'; is showing 900,000, which seems more then enough as one table has about 16,000 and the other 10,000 records, unless its a multiplication of them both, I will then need to up it to above 160,000,000? I used SET MAX_JOIN_SIZE = 4294967295 but it still shows on 900000, maybe something set by the hosting provider? You where on the right track the whole time :) – mflammia Jan 24 '13 at 13:42