indices with more than one column

[Former Member]

Hi,

I want to create an index for multiple columns on a table. Is the order important in which the column-names in a CREATE INDEX-statement are given?

[Former Member · Accepted solution]

Thank you for the fast answer, but I did not understand it at all:

Assume there is a table with columns id, a1, a2 and a3 with primary key id.

Can you please tell me more about the difference between

CREATE INDEX indexA ON thetable a1, a2

and

CREATE INDEX indexB ON thetable a2, a1

?

Is indexA better than indexB for the following select-statement?:

SELECT * FROM thetable where a1 = 1 and a2 = 2

Maybe it's a stupid question^^ but I am new on this subject

[lbreddemann · Accepted solution]

> Thank you for the fast answer, but I did not understand it at all:

> Assume there is a table with columns id, a1, a2 and a3 with primary key id.

>

> Can you please tell me more about the difference between

> CREATE INDEX indexA ON thetable a1, a2

> and

> CREATE INDEX indexB ON thetable a2, a1

> ?

> Is indexA better than indexB for the following select-statement?:

> SELECT * FROM thetable where a1 = 1 and a2 = 2

For your given statement both indexes are totally equal.

There's no functional difference for this statement.

The statement however is maybe not so wisely chosen.

Suppose you've a statement like this:

SELECT * FROM thetable where a1 < 1000 and a2 =>0

And let's also assume that a1 contain values from 0 to 1000000 and a2 1000000 to 0.

Now, which index will be better?

If indexB is used there would be a range scan over the complete first column and every now and then a a1 fitting to the condition will be found.

If indexA is used a small range scan for the fitting 1000 rows will be done (just a few pages to read here) and the condition on a2 will be evaluated on the fly afterwards.

Or, a much simpler example.

SELECT * FROM thetable where a2= 1000

Which index is better?

You see: index-design depends totally on how you want to access your data!

Make sure to check the optimizer documentation in the SDN MaxDB WIki, in the official documentation and in the internals course material on [MaxDB Homepage|http://maxdb.sap.com].

regards,

Lars

[Former Member]

Ah ok, thank you very much.

Now I understand a little more^^

(and I'll read the documentation)

[lbreddemann]

> I want to create an index for multiple columns on a table. Is the order important in which the column-names in a CREATE INDEX-statement are given?

Of course the order is important.

When you choose it in a way that indexed fields cannot be used for your statements then the index is pretty much worthless.

Therefore: check your statements you want to support with the index and order the columns appropriately.

E.g. a common them is to include columns of the primary key into the index definition.

Don't do that!

The primary key is always already part of any secondary index in MaxDB, as it serves as the row-reference.

From a logic point of view every index has the fields (COL_X, COL_Y....) + (KEYCOL_1, KEYCOL_2...) stored in it.

regards,

Lars

Edited by: Lars Breddemann on Jan 18, 2010 3:48 PM