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:
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
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
#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
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
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