db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: What is performance differenc(es) between 1 index per column or one index for multiple column?
Date Wed, 11 Jan 2006 19:33:40 GMT
As Ryan says, Derby can only use the index if all the leading columns
in a multi-column index are part of the where clause.  Note that it
can use it if only 2 leading of 3 columns are available.  Often such
an index is created so that it is a "covering" index so that the
results of a select can be returned just from the index without going
to the base table (this covering aspect is much less important than
making sure you have useful indexes for your query - I would only look
at this as a secondary tuning).

As Jeff points out Derby maintains a separate file for each base table
and each index, so an update that affect an index column will have to
update the base table and index table.  A delete will have to change
every index and the base table.

Ryan Bobko wrote:
> As with everything in databases, performance depends on what you're
> doing. (What follows is general database information, not Derby
> specific.) If you have a query like:
> 
> select bannerbannerid
> from SSiteRequest
> where BANNERBANNERID=6 
> and WEBSITESWEBSITEID=10 
> and USERSUSERID=1
> 
> then the composite index will probably be faster. However, if your
> statements are like:
> 
> select bannerbannerid
> from ssiterequest
> where useruserid=3
> 
> then the second index scheme will work better. In fact, in this example,
> the first index couldn't be used at all because it's not the first field
> in the index. (Which isn't always strictly true anymore. I know Oracle
> can use secondary fields like a primary field in some situations.)
> 
> ry
> 
> 
>>
>>Hi
>>Thank you very much for reading my post.
>>can you please explain me what is differences between 1 index for one 
>>column and one index for multiple columns ?
>>in both performance view and technical differences.
>>
>>example :
>>
>>
>>create index Index1 on SSiteRequest (BANNERBANNERID, WEBSITESWEBSITEID, 
>>USERSUSERID);
>>and
>>
>>create index Index13 on SSiteRequest (USERSUSERID);
>>create index Index12 on SSiteRequest (WEBSITESWEBSITEID);
>>create index Index1 on SSiteRequest (BANNERBANNERID);
>>
>>
>>does this two kind differ from each other ?
>>which one will have better performance?
>>
>>
>>
> 
> 
> 
> 
> 


Mime
View raw message