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 » Mon, 30 Jan 2006 23:13:11



Hi,

First of all, thanks a lot for your detailed help!

You were correct in your words about having foreign key from Z_EVENTS
(EVENT_ID, EVENT_TYPE) to EVENTS (EVENT_ID, EVENT_TYPE). The reason I've put
EVENT_TYPE also in Z_EVENTS is so that I could use the EVENT_TYPE = 2
constraint in the Z_EVENTS table (I know it's redundant but this was done to
prevent accidantly inserting rows to Z_EVENTS which are not of type 2
(EVENT_TYPE = 2). Anyway, I'm afraid to remove it now when the DB is already
working and the cost is not that big).

In my DB there are not that much INSERTS and UPDATES so many indexes are not
such a problem.

Your words about the selectivity of EVENT_DATE, EVENT_TYPE and
EVENT_DEPARTMENT are true also in my DB. However, I am having a difficulty
to understand how could scanning 1,000 rows of all the departments could be
faster than using index and scanning only 200 rows in a case when each row
which is being scanned has about 20 different varchar text fields to search
in with LIKE '%SearchedText%' in the WHERE clause, as I mentioned in:


I mean, this makes each 'scan' of a single row a very expensive action in
terms of performance.

You've mentioned using a compound index (EVENT_DATE, EVENT_DEPARTMENT). If I
use ONLY the compound index, what happens if the user searches the DB with
restriction only about the EVENT_DATE field (and no restriction of the
EVENT_DEPARTMENT)?

In my DB, I think that about 90% of the EVENT_TYPE fields are 1, and the
rest 10% is 2 (Havn't checked yet since I don't have access to the DB from
here). Should I not use index on EVENT_TYPE even with such a situation, even
if I know that most of the queries are restricting to EVENT_TYPE=2 (which
means searching on 10% of the rows)?

You've mentioned that:

I'm confused. On one hand you've confirmed in your answer that the optimizer
could combine indexes of different fields when it is relevant for a curtain
query. On the other hand you've adviced me to make a compound index. So,
guessing I have 2 fields with high selectivity, Which of the following
options is the best?
1. having only a seperate index of each of the 2 fields.
2. having only a compound index of both the fields.
3. both 1 and 2.

Kind Regards,
Amir.
 
 
 

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. struture accessing, struct, fields, index, indexing

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

9. Multifield Index and Single field index

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

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

12. Index field code displays, not index entries

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

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

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