db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ryan Bobko" <r...@ostrich-emulators.com>
Subject Re: What is performance differenc(es) between 1 index per column or one index for multiple column?
Date Mon, 09 Jan 2006 21:40:53 GMT

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