MySQL Forums
Forum List  »  Optimizer & Parser

inner join on range criteria: unable to use index?
Posted by: Joseph Fisk
Date: August 11, 2008 04:34PM


Even after searching for a while I'm having trouble understanding why this query is not faster:

select * from s inner join r on r.tstart >= s.chrstart and r.tend <= s.chrstop and r.tname=s.chr and r.strand=s.chrstrand;

tstart/tend/chrstart/chrstop are all bigint, and of the columns involved in the join, they are the most selective.

I would expect mysql to scan the smaller table, and then take advantage of a multi column index on the larger table on (start, stop, chr, strand), which is in order from most to least selective.

But mysql won't use that index. Why not?

Options: ReplyQuote

Written By
inner join on range criteria: unable to use index?
August 11, 2008 04:34PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.