My understanding is that the multi column index I described would work with just IS NULL or IS NOT NULL.
The where clause has to hit the index columns in the order they are defined in the index. BUT, if you only ask for the first column it will work too. Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Wednesday, October 31, 2012 10:16 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Indexing question Dennis and Razzak: I read Razzak's fte article (thanks for the heads-up!) and see that "is null" is one of the operands that is used by indexing, so that answers that question. In this case, the table has 85000 rows, and 1300 of them are null so that's 1.5% Dennis: I'm not sure how a multi-column index would work. My understanding is that you must reference both columns in a multi-column index in order for the index to be used (the fte article doesn't talk about multi-column very much). So let's say I have a PK column. Would I have to say "give me all rows where DateTime is null and PKColumn is not null?" Karen In a message dated 10/31/2012 9:49:56 AM Central Daylight Time, [email protected]<mailto:[email protected]> writes: When a column has a lot of duplicate values (nulls in this case), and I MUST have an index, I use a multi-column index where the column in question is first and the second column is one that is very unique. That said, I try to avoid multi-column indexes except where I absolutely must have them. This sounds like one of those. If you are searching for null or not null without an index, the whole table must be traversed to find what you want. The index avoids traversing the table so would be effective in both cases. Dennis McGrath
