CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
azwr
azwr

Reputation: 21

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.

Upvotes: 1

Views: 11913

Answers (3)

Md. Shahadat Hossain Choudhury
Md. Shahadat Hossain Choudhury

Reputation: 11

You can try:

$query = "SELECT ... ";

mysqli_query($databaseConnection, "SET OPTION SQL_BIG_SELECTS=1");

$results = mysqli_query($databaseConnection, $query);

Upvotes: 0

Chinmay235
Chinmay235

Reputation: 3414

Try running as a query previous executing your main query

mysql_query("SET SQL_BIG_SELECTS=1");

Upvotes: 1

azwr
azwr

Reputation: 21

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

Upvotes: 1

Related Questions