I have a query I’m attempting to optimise.
table1 has 300,000 rows and table2 has 100,000 rows
Each table has key columns (key1..key15)
I am attempting to select all rows in table1 and LEFT JOINing table2 on these matching keys.
I also have wildcard keys in table2 (designated ‘all’)
Here is a partial example of the join
SELECT
t1.*,
t2.*
FROM table1 t1
JOIN table2 t2 ON
(t1.key1 = t2.key1 OR t2.key1 = ‘all’) AND
(t1.key2 = t2.key2 OR t2.key2 = ‘all’) AND
(t1.key3 = t2.key3 OR t2.key3 = ‘all’) AND
(t1.key4 = t2.key4 OR t2.key4 = ‘all’) AND
(t1.key5 = t2.key5 OR t2.key5 = ‘all’) AND
…
(t1.key15 = t2.key15 OR t2.key15 = ‘all’)
It appears that whilst the key to key match uses the indexes, the ‘all’ search does not.
I have indexed all keys individually and compound keys (I.e [key1, key2, …]
The planner appears to still require a full table scan of table2 for each row in table1
Further Clarification
table2 is essentially a lookup table which is guaranteed to always return at least 1 match (based on the join) to a row in table1
keys are text e.g aaa bbb ccc (in table1) or aaa bbb ccc or all (in table2)
e.g
table1: aaa bbb ccc would match the following in table2 [aaa bbb ccc, aaa all ccc, aaa all all etc…]