Preventing runaway MySQL queries

If you use a MySQL database with large tables, it’s possible to accidentally run queries that try to sort millions of rows (usually through some kind of programming error, such as an “unconstrained table join”).

Those runaway queries can slow down the MySQL server for many minutes on end, causing performance problems.

To prevent the worst of that, we’ve set the max_join_size setting to 1,000,000 on our MySQL servers.

This shouldn’t affect any normal queries, but queries that attempt to sort more than a million rows will now return this error:

ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

If you have any questions, please contact our support staff.

Update March 28, 2011: A customer pointed out that another situation that can cause this error is joining two tables on fields without indexes. If your SQL query looks like “WHERE a.field_a = b.field_b”, and you don’t have indexes on both field_a and field_b, MySQL has to first construct a huge temporary table that can contain millions of rows (for example, joining two 3,000 row tables without indexes can require a 9,000,000 row temporary table). Adding indexes to both join fields will fix this.