Board index SQL SERVER

Using multiple field index when each of the fields already has seperate index

Using multiple field index when each of the fields already has seperate index

Post by Amir » Tue, 31 Jan 2006 03:59:19


ert-Jan, Thanks a lot for your answers!

If I'm using a compound index on (EVENT_DATE, EVENT_TYPE), and then I'm
running a query which retrieves rows from a specific range of dates, but
without restriction on the EVENT_TYPE, will the optimizer be able to use
that index?

At first I thought the answer is yes:

But later you advice me to choose option [3], which means having both:
a. index on EVENT_DATE
b. index on EVENT_TYPE
c. compound index (EVENT_DATE, EVENT_TYPE)

Why do I need the seperate indexes on EVENT_DATE (a) and EVENT_TYPE (b) if I
have a compound index on (EVENT_DATE, EVENT_TYPE) (c)?
I think that this is necessary only if the user runs queries which are using
only EVENT_TYPE restriction and not EVENT_DATE restriction (since such
queries cannot use the (EVENT_DATE, EVENT_TYPE) index because of the order
of the fields in the index). Is that correct?

I can assure that the users will not run any 'wide' queries without dates
restriction, so that I can assure the EVENT_DATE will always be in the WHERE
clause. In that situation can I not use the seperate indexes on EVENT_DATE
and EVENT_TYPE at all, and use only the compound index instead (assuming the
EVENT_DATE will always be in the WHERE clause, and that the EVENT_TYPE will
be used if needed, according to the user's query and the statistics)?

Kind Regards,
Amir.

"Gert-Jan Strik" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 
Top

1. DISPLAYING KEY INDEX FIELDS WITH MORE THAN ONE FIELD THAT CAN CONTAIN MULTIPLE VALUES

2. Indexing a table on multiple fields with a date field in descending order

I have a table with the following fields: id, date, testrecord. What I want
to do is to order the table so that the records are ordered as follows: id
by date by testrecord. I want id to be in ascending order, but date and
testrecord in descending order. I created an index file called testdata with
the expression: id+dtoc(date,1)+testrecord. Id and testrecord are character
fields.This index file will sort in ascending order. My question is: what do
I need to do to get the table sorted as I want it - that is id in ascending
order, but date and testrecord in descending order?

3. Pivot Table - Add calculated field using field number (index) vs n

4. Seek using an index with multiple Replication ID fields

5. Using a multiple field index in a form

6. Multiple document index uses docs showing fields

7. use array index or values as index to fields to print

8. struture accessing, struct, fields, index, indexing

9. Index field code displays, not index entries

10. Multifield Index and Single field index

11. WHERE on non-indexed field claims to use PKEY index?

12. CREATE INDEX question (how to index on "money" field?)

13. ClientDataSet Index Not Finding a '-' in an indexed field

14. Multiple indexes using index.sty & hyperref & TeX4ht

15. Making multiple indexes with index package while using natbib's \citeindextrue command

All times are UTC
Board index