Thanks -- I just tried your suggestion.
I indexed: Visible, order_id, ticket_id
and
explain SELECT type FROM orderhistory FORCE INDEX (`visible_order_id_ticket_id`) WHERE (order_id = '944704' OR ticket_id = '944704') AND visible = 'Y' limit 1
Code:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orderhistory ref visible_order_id_ticket_id visible_order_id_ticket_id 3 const 1922326 Using where
we now have a faster query (but it's still scanning 1.9 million rows for some reason).
If I removed and visible = 'Y' I get a good quality query:
explain SELECT type FROM orderhistory WHERE (order_id = '944704' OR ticket_id = '944704') limit 1
Code:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orderhistory index_merge order_id,lookup order,ticket_id lookup order,ticket_id 4,4 NULL 5 Using sort_union(lookup order,ticket_id); Using where
What am I missing? I've googled so hard for the solution here.
I should have paid more attention in database class
