Support Start > Web Hosting Technical Information > MySQL Databases > MySQL “MAX_JOIN_SIZE” Error

If you run a MySQL database query that would act on more than 1 billion rows, you’ll see an error message saying something like:

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 MAX_JOIN_SIZE=#
if the SELECT is okay

This happens because queries that examine more than a billion rows usually have an error, and can cause problems for your database if they’re allowed to run.

One 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 very large numbers of rows (for example, joining two 50,000 row tables without indexes can require a 2.5 billion row temporary table). Adding indexes to both join fields will fix this.

If you’re positive you need to run a query this large and want to try it anyway, running a SET SQL_BIG_SELECTS=1 statement first will skip the check that prevents them. Keep in mind that it’s likely to fail after some time anyway, though; you should closely examine your SELECT statement for errors before trying this.

If you experience this problem and aren’t sure how to solve it, you can contact us and tell us the exact query that’s causing trouble, and we can probably help you fix it.