sql - MySQL indexing in an "or" statement -

i have 3 tables need join, these join fine using indexes. however, transitioning on using 1 legacy field identifier 1 in table. legacyid legacy field, while newid new field. both fields varchars. both fields indexed exclusively btree index, both tables myisam.

select username     cust c use index(primary,newid)   join tblshp s on s.cusid = c.cusid   join tblq q on q.shpid = s.shpid  c.legacyid = '692041'      or q.newid = '692041' 

this query takes 5.147 seconds, that's 5 seconds longer expect.

when doing explain extended query index type newid i.e. full table scan , possible keys (primary,newid) , key(null). if remove legacyid or statement, explain says key (newid) used. if remove newid or statement changes occur following:

• the type of table joins (s,c) change type ref eq_ref
• key_len changes 4 5 (on both)
• extra changes empty "using where" . either 1 of statements removed the or statement query runs @ expected speeds.

table q has 183k records; c:115k; s:169k. 1 last point. if move query placement:

   select username        cust c use index(primary,newid)      join tblshp  s on s.cusid = c.cusid left join tblq q on q.shpid = s.shpid                  , q.newid = '692041'     c.legacyid = '692041'  

although not same query, way data works, provide results need, , speed down under .1 of second again.

i did want clarify don't need query works solution. ponies below has provided one. need know if else has run problem , can explain why happening , can simple or statement use both indexes.

if know there won't duplicates, change union union all (union all faster because doesn't remove duplicates). otherwise, use:

select username     cust c use index(primary,newid)   join tblshp s on s.cusid = c.cusid   join tblq q on q.shpid = s.shpid  c.legacyid = '692041'  union select username     cust c use index(primary,newid)   join tblshp s on s.cusid = c.cusid   join tblq q on q.shpid = s.shpid  q.newid = '692041' 

ors notoriously bad performers, because splinters execution path. union alleviates splintering, , combines 2 results sets. said, in preferable ors because though being logically same, execution of in more optimized.

union isn't answer

investigate many options, comparing explain plan output before determining solution. i've come across couple perform better using cursor single query using esoteric functionality.

also, make sure foreign key columns (what you're using in on clause when joining) indexed. mysql has started (v5.5+?) automatically when foreign key constraint made, that's innodb tables.