Compound index vs. multiple single indexes

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Compound index vs. multiple single indexes

Post by barry.marcus »

We have a table from which data is retrieved in only one place in our application. That query is as follows:

select document_hit
from validation_result
where patent_wku=$thisDocument
and project_id=$project
and search_field=$saveField
and filter_id=$filterId

This is a new table and does not yet have much data in it. But I expect to grow to be very large. My question is does it make more sense in the long run to have a single, compound index on (patent_wku, project_id, search_field, filter_id) or four indexes, one for each column. Or perhaps it doesn't make any difference. BTW, this are obviously standard (i.e., non-metamorph) indexes I'm talking about.

Thanks.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Compound index vs. multiple single indexes

Post by John »

Assuming that you always provide values for all criteria, i.e. don't use $null to drop clauses the single compound index is best.
John Turnbull
Thunderstone Software
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Compound index vs. multiple single indexes

Post by barry.marcus »

In the case of this particular table, this will always be the query. Moreover, it makes no sense for any of the fields to ever be null.

So, thanks... a single compound index it is!
Post Reply