azwr azwr - 6 months ago 32
MySQL Question

MySQL error #1104 - The SELECT would examine more than MAX_JOIN_SIZE rows;

I have a database for donor and ticket sale information for a small non-profit. I'm trying to get a quick mailing list export based on people who have donated, bought a season ticket, or bought a single ticket. The "entity" table is the contact info, etc, and then the other tables hold info about the donation (year, amount, check date, etc) and has a field for "entityno" which matches it up to the primary key of entity.recordno.

Here's the query I'm running:

SELECT *
FROM
entity
LEFT JOIN individual_donation ON entity.recordno = individual_donation.entityno
LEFT JOIN season_tickets ON entity.recordno = season_tickets.entityno
LEFT JOIN single_tickets ON entity.recordno = single_tickets.entityno
WHERE
entity.ind_org = 'ind' AND
entity.address1 <> "" AND
(individual_donation.year <> 'NULL'
OR season_tickets.year <> 'NULL'
OR single_tickets.year <> 'NULL')
GROUP BY entity.lastname
ORDER BY entity.lastname ASC


This database is on BlueHost, and I'm accessing it through PHPmyadmin. The strange thing is that the query runs just fine when I preview it in PHPmyadmin - it returns 216 rows, and I can view all the rows within the SQL command browser and it loads just fine.

The problem is that every time I use PHPmyadmin's "export" command under the query results operations, 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


Each of the tables is only about 300-400 rows at most, so I'm surprised that I'm getting a MAX_JOIN_SIZE error. It's also really strange to me that the sql query works just fine as is, but won't work on the export??

I'm sure I could do better JOINs etc, but I don't understand why the query runs fine, but just won't export.

EDIT:
here's the EXPLAIN EXTENDED result

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE entity ALL NULL NULL NULL NULL 429 100.00 Using where; Using temporary; Using filesort
1 SIMPLE individual_donation ALL NULL NULL NULL NULL 221 100.00
1 SIMPLE season_tickets ALL NULL NULL NULL NULL 102 100.00
1 SIMPLE single_tickets ALL NULL NULL NULL NULL 217 100.00 Using where


Further Information:
Strange - my webhost doesn't allow FILE permissions for mysql users, so I can't use EXPORT INTO. I tried using ssh access, running the query to > into a file, and I get the MAX_JOIN_SIZE error. I still don't understand why it would work in the phpmyadmin query in the browser just fine, but not export in phpmyadmin, nor work from the command line.

Answer

Doing a better job with indexes seems to have solved my problem, although still not sure why.

I made sure that the "entityno" column in the three referenced tables, which is the reference to the primary key in the entity table, were set as indexes. This seems to have solved whatever was causing the large number of returned rows in some intermediate step with my query. For reference, this is now the explain extended result:

id      select_type     table                   type    possible_keys   key             key_len ref                             rows    filtered        Extra
1       SIMPLE          entity                  ALL     NULL            NULL            NULL    NULL                            429     100.00          Using where; Using temporary; Using filesort
1       SIMPLE          individual_donation     ref     entityno        entityno        3       dakotask_ds1.entity.recordno    3       100.00
1       SIMPLE          season_tickets          ref     entityno        entityno        3       dakotask_ds1.entity.recordno    2       100.00
1       SIMPLE          single_tickets          ref     entityno        entityno        3       dakotask_ds1.entity.recordno    1       100.00          Using where
Comments