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
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.