I noticed that one of my queries was pretty slow, taking about two seconds to execute.
This is the slow query:
SELECT EVENT_DATETIME, EVENT_EVENTTYPE FROM EVENT WHERE (EVENT_ID = :1 AND EVENT_RESULT = :2 )
The index used was created like this:
CREATE INDEX "INDEX_EVENT_DATETIME" ON "EVENT" ("EVENT_DATETIME", "EVENT_ID")
The execution plan shows an
index skip scan because obviously the
EVENT_DATETIME column is not in the where clause.
So at first I thought I would create a second concatenated index on the table for the columns
(EVENT_ID, EVENT_RESULT) but then I noticed that the column
EVENT_DATETIME has 18,702,145 distinct values. Of a total of 18,722,706 rows.
There are 4 different queries happening regularly on this table. All of them except one have the
EVENT_DATETIME column in the where clause but all of them have
EVENT_ID in the where clause.
So my assumption is that the index as it is currently being used doesn't make the queries more efficient.
Is it correct that a concatenated index on the columns
(EVENT_ID, EVENT_DATETIME) would be more useful?