db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Damian Carey <jami...@gmail.com>
Subject When is multi field index preferable over single field ?
Date Sat, 12 Jun 2010 05:58:25 GMT
Hello,

We have a simple database, only 3 tables with a few fields in each)
that contains data supplied by a third party. Almost entirely read
only.

The table of concern has about 380K rows, and on each retrieval we
will retrieve between 10 and 500 rows.

I am looking for some BASIC advice on which indexes should be created
to assist with standard queries.

I am just looking for reasonable advice, and am not worried about any
super optimal solution.

The two basic queries are as follows ...

SELECT * FROM MYTABLE qt WHERE qt.scheduleId = 'ABC' AND qt.body = '4DR'

and

SELECT DISTINCT qt.body FROM MYTABLE qt WHERE qt.scheduleId = 'ABC'

Are "multi field" indexes preferable or would single field indexes work OK.

Perhaps something like this for a multi field ....

CREATE INDEX T_SCHED_BODY_IDX ON MYTABLE (scheduleId, body)

Or perhaps something like this for a single field ....
CREATE INDEX T_SCHED_IDX ON MYTABLE (scheduleId)
CREATE INDEX T_BODY_IDX ON MYTABLE (body)

Any guidance, advice, clues, suggestions, experience would be most appreciated.

Many thanks,
-Damian

Mime
View raw message