Will composite indexes or unique indexes help with query performance? I need help with identifying indexes to increase performance of the following query. I'm currently using v5.5 with InnoDB. I cannot modify the query. However, I can add indexes as needed. The following indexes are currently available:


t1.id = Primary key
t1.status
t2.owner
t2.user
t2.total

select t1.`id` 
from (tbl1 t1 inner join tbl2 t2 on t1.`id` = t2.`id`) 
where (t2.`owner` = `ABC123` or t2.`user` = `ABC123`) 
  and t1.status = 1 
order by t2.`total` 
limit 0, 100;
(t2.`owner` = 'ABC123' or t2.`user` = 'ABC123') 


This search term will force a table-scan. Composite indexes won't help.

Suppose you had a composite index on (owner, user). It could do a quick lookup for owner = 'ABC123' but then the desired values for user could be anywhere in the table, not just in the subset matching the desired owner value. So it has to scan the whole table anyway.

Likewise if you had a composite index on (user, owner). It could do a quick lookup for user = 'ABC123' but the same problem exists, finding the rows for owner will force a table-scan.

The one remaining possibility is to use index merge union optimization, but in my experience this doesn't work as well as you'd think.