I think you have the essence of the problem: MySQL can't decide WHICH index to use when you are looking at an OR condition.
One of the HUGE problems with MySQL is that it can *NOT* use just a PART of an index unless that PART is at the beginning of the index.
So if you had (for example) an index on ( orderi_id, ticket_id, visible2 ) [all 3 fields], MySQL would NOT be able to use that index with your OR where clause *except* for order_id
. It wouldn't be able to use ( order_id, visibie2 ) because the "parts" there are not contiguous. It wouldn't be able to use ( ticket_id ) at all, because that "part" isn't at the start of the index.
It's a real deficiency in MySQL, not shared by all other DBs.
So your only real choice is to have two indexes: ( order_id, visible2 ) and ( ticket_id, visible_2 ) and then, as you noted, make two separate queries.
You *could* do:
SELECT U.* FROM
( SELECT * FROM orderhistory WHERE order_id = 555444 AND visible2 =1
SELECT * FROM orderhistory WHERE ticket_id = 555444 AND visible2 =1
) AS U
And that should work, assuming you ndeed have two indexes: (order_id, visible2) and (ticket_id,visible2).
But it won't likely be the most efficient way. Assume, for the sake of argument, that indeed you find a match based on order_id and visible2. With the UNION query, MySQL will *still* have to make the second query based on ticket_id.
So, indeed, the two separate queries is likely the best choice. If you have *ANY* inkling which is more likely to match--ticket_id or order_id--then make that query first, so you only have to make the second less likely query if you don't get a match on the first.
NOTE: You *could* do all this in a STORED PROCEDURE. And if you really have no idea which is most likely to match (ticket_id or order_id) then that would be a fine choice. But, again, if you do have (say) some way of guessing which is more likely in any given circumstance, you'd be better off with the two separate queries, in most-likely order.