Re: Which index is fastest

In article <919790570.2470.0.nnrp-06.c1ed1f69@news.demon.co.uk>, "Tony Flaherty" <aef@mfs.misys.co.uk> wrote: > > Manel Falc' wrote in message <36c41fc1.15758373@news.bcn.ttd.net>... > >Hi, > > > >having the table: > > > > create table orders > > (company smallint, > > customer char(6), > > num_order integer, > > ... > > )> > > >Which index is fastest in order to find ONE row ? > > > > create index orders_1 on order (company, customer, num_order);> > or > > create index orders_1 on order (num_order, customer, company);> > > >and doing: > > select * from orders > > where company = 1 and customer = "0001" and num_order = 1000> > > >for few companys, and lots of customer and orders ... > > > > This is an interesting question, I tend to create indexes with the most > significant (changes the least) element first, unless of course I know that > SELECTs are going to be looking at it differently. Mostly this is because > you will want to order by this for reports etc. But that's not finding just > one row, I guess for that an index starting with num_order would be better. > Anyone know an authoritative answer to this? > > Tony Flaherty > Misys Financial Systems > All statements and opinions are my own, > Misys don't pay me enough to have opinions > on their behalf. > > Not being an authority on Informix..... But, in the general case of searches and sort-trees... I would think that a index where the most significant column changes the MOST would lead to an overall better balanced tree Consider the case where you choose the "state" as the first column of the composite key. Now consider that 99% of your customers are from the same state. Your tree quickly becomes unbalanced, and may degrade into a linear search. Of course, this is in a general case, not all DBMS are created equal. YMMV Vince Pachiano -- There is no satisfactory substitute for excellence. -- Of course it can be done. It's only software! -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

For whatever is worth! We use composite indices extensively. The best results are achieved with whatever combination makes the COMPOSITE index more unique! VP wrote: > > In article <919790570.2470.0.nnrp-06.c1ed1f69@news.demon.co.uk>, > "Tony Flaherty" <aef@mfs.misys.co.uk> wrote: > > > > Manel Falcó wrote in message <36c41fc1.15758373@news.bcn.ttd.net>... > > >Hi, > > > > > >having the table: > > > > > > create table orders > > > (company smallint, > > > customer char(6), > > > num_order integer, > > > ... > > > )> > > > > >Which index is fastest in order to find ONE row ? > > > > > > create index orders_1 on order (company, customer, num_order);> > > or > > > create index orders_1 on order (num_order, customer, company);> > > > > >and doing: > > > select * from orders > > > where company = 1 and customer = "0001" and num_order = 1000> > > > > >for few companys, and lots of customer and orders ... > > > > > > > This is an interesting question, I tend to create indexes with the most > > significant (changes the least) element first, unless of course I know that > > SELECTs are going to be looking at it differently. Mostly this is because > > you will want to order by this for reports etc. But that's not finding just > > one row, I guess for that an index starting with num_order would be better. > > Anyone know an authoritative answer to this? > > > > Tony Flaherty > > Misys Financial Systems > > All statements and opinions are my own, > > Misys don't pay me enough to have opinions > > on their behalf. > > > > > > Not being an authority on Informix..... > But, in the general case of searches and sort-trees... > I would think that a index where the most significant column > changes the MOST would lead to an overall better balanced tree > > Consider the case where you choose the "state" as the first column > of the composite key. Now consider that 99% of your customers > are from the same state. Your tree quickly becomes unbalanced, > and may degrade into a linear search. > > Of course, this is in a general case, not all DBMS are created equal. > YMMV > > Vince Pachiano > -- There is no satisfactory substitute for excellence. > > -- Of course it can be done. It's only software! > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own