EverSQL Database Performance Knowledge Base
Making a conditional MySQL join involving a small table and a large table efficient
Database type:
The MySQL query I'm currently trying to perform is functionally equivalent to this:
SELECT small_table.A, small_table.B, small_table.C, huge_table.X, huge_table.Y
FROM small_table LEFT JOIN huge_table
ON small_table.D = huge_table.Z
WHERE small_table.E = 'blah'
except that the query doesn't appear to terminate (at least not within a reasonable amount of time), probably because the second table is huge (i.e. 7500 rows with a total size of 3 MB). Can I perform a functionally equivalent join in a reasonable amount of time, or do I need to introduce redundancy by adding columns from the huge table into the small table. (I'm a total beginner to SQL.)
The clause WHERE small_table.E = 'blah' is static and 'blah' never changes.
Here is the EXPLAIN output as requested:
Array ( [0] => Array ( [0] => 1 [id] => 1 [1] => SIMPLE [select_type] => SIMPLE [2] => small_table [table] => small_table [3] => ref [type] => ref [4] => E [possible_keys] => E [5] => E [key] => E [6] => 1 [key_len] => 1 [7] => const [ref] => const [8] => 1064 [rows] => 1064 [9] => Using where [Extra] => Using where ) [1] => Array ( [0] => 1 [id] => 1 [1] => SIMPLE [select_type] => SIMPLE [2] => huge_table [table] => huge_table [3] => eq_ref [type] => eq_ref [4] => PRIMARY [possible_keys] => PRIMARY [5] => PRIMARY [key] => PRIMARY [6] => 4 [key_len] => 4 [7] => my_database.small_table.D [ref] => my_database.small_table.D [8] => 1 [rows] => 1 [9] => [Extra] => ) )