Mysql: "The Select Would Examine More Than Max_Join_Size Rows"

SELECT would examine more than MAX_JOIN_SIZE rows

you can simply issue a query containing the SET command:

 $this->db->query('SET SQL_BIG_SELECTS=1'); 

The SELECT would examine more than MAX_JOIN_SIZE rows

SELECT  A.ID, B.ID, C.ID
    FROM  A
    INNER JOIN  B  ON A.ID = B.aID
                  AND A.providerID = B.providerID
    INNER JOIN  C  ON A.ID = C.aID
                  AND A.providerID = C.providerID
    WHERE  A.providerID = 200

and add

A:  INDEX(providerID, ID)   -- In this order
B:  INDEX(providerID, aID)  -- In either order
C:  INDEX(providerID, aID)  -- In either order

If possible, please use the real names for tables and columns; sometimes there are useful clues of the intent which can help in dealing with performance (etc) issues. If they weren't all "200", then my answer needs to have volcanic ash spewed onto it.

Probably the 'join' limit can be avoided by having these indexes.

It seems that B and C are strongly related; is there a reason to have two separate tables? (Again, actual names might have answered this question.)

Codeigniter The SELECT would examine more than MAX_JOIN_SIZE rows

You can simply issue a query containing the SET command:

$this->db->query('SET SQL_BIG_SELECTS=1'); 

MySQL MAX_JOIN_SIZE errors

MAX_JOIN_SIZE is a safety catch commonly used on the shared hostings.

It won't let you accidentally run long queries which would hang the server.

Issue this command:

SET SQL_BIG_SELECTS = 1

before running the query you know to return lots of values.

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

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

MySQL MAX_JOIN_SIZE Error! Need to optimize query

You should use a JOIN, and not just select from all tables. If you select from all tables, all possible combinations of rows are generated (and this are A LOT) and then the 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 can't see any obvious problems in your query. I would just set the options as the error message tells you and then look if the result is the one you wanted to get. If it is - fine. If it isn't - come back and tell us.

MySQL - SQL_BIG_SELECTS

1. MySQL determines whether or not a query is a 'big select' based on the value of 'max_join_size'. If the query is likely to have to examine more than this number of rows, it will consider it a 'big select'. Use 'show variables' to view the value of the max join size.

2. I believe that indexing and particular a good where clause will prevent this problem from occuring.

3. SQL_BIG_SELECTS is used to prevent users from accidentally executing excessively large queries. It is okay to set it to ON in mysql.cnf or using the command-line option at startup.

4. You can set SQL_BIG_SELECTS in my.cnf or at server startup. It can also be set on a session basis with SET SESSION SQL_BIG_SELECTS=1.

5. Not that I can think of. I would just check your query to make sure that you really need to use it. Our servers have it turned on by default, and max_join_size is very large.

---

Related Topics