MySQL Forums
Forum List  »  Oracle

Re: Using "IN" in the WHERE clause
Posted by: Nick Roper
Date: July 31, 2004 05:28AM

Allen,

I'm not exactly sure how the optimizer will use indexes for an IN(list) expression, but my guess is that it will depend on the list of items. For example, if the expression were WHERE cha.unitid IN(1,3,7,9), and there is an index on the unitid column, then the optimiser may well be able to use the index to elimate any values < 0 or > 9 and then just examine the remaining rows.

As I say, I'm not sure, but someone else might know.

One thing that you could do is to use the EXPLAIN keyword in front of the query, e.g:

EXPLAIN SELECT SUM(bla), substring(cha.day,1,10) bhour,
etc....

This should display output that will show how the optimizer uses indexes for the query. For more information, see:

http://dev.mysql.com/doc/mysql/en/EXPLAIN.html

You could try it with both cha.unitid = 1 and cha.unitid IN(1) and see what differences there are.



--
Nick Roper

Options: ReplyQuote


Subject
Views
Written By
Posted
4360
July 29, 2004 12:52PM
Re: Using "IN" in the WHERE clause
2656
July 31, 2004 05:28AM


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.