Karen, 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 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 9:24 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Indexing question I have a datetime field in a large table where, obviously, almost every entry has a different value. We never search for a specific value except for maybe a DEXTRACT where I assume an index on the field would be useless unless we add a computed column for that value, right? But we often search where the field is not null or null. Do indexes help with a "is null"? And if they would, I assume I would decide whether to index based on how many are null; if 50% are null then an index probably wouldn't help but if only 5% are null then it might. Karen
