What index ?

Arnaud de Montard (5/1/14 5:59PM)
Keisuke Miyako (5/2/14 1:28AM)


Arnaud de Montard (5/1/14 5:59 PM)

I don't have any idea of what is the best choice for index type of
foreign keys (FK). I did that:

QUERY($table_p->;$FK_p->#0)
DISTINCT VALUES($FK_p->;$val_al)
$ratio_r:=Size of array($val_al)/Records in selection($table_p->)
If ($ratio_r>0,5)
$idxType_l:=Standard BTree Index
Else
$idxType_l:=Cluster BTree Index
End if
$fieldIndex_ap{1}:=$FK_p
$indexName_t:=Field name($FK_p)+"_idx"
CREATE INDEX($table_p->;$field_ap;$idxType_l;$indexName_t)

Some better ideas?

--
Arnaud de Montard

Keisuke Miyako (5/2/14 1:28 AM)

Hello,

jump to slide 138,

http://fr.slideshare.net/kmiyako/
(it?=A2?=82®=E2ÑĘs in Japanese but the charts come from
Thibaud?=A2?=82®=E2ÑĘs presentation)

it depends on which operation you perform more, DISTINCT VALUES or
ORDER
BY.

"clusters are always faster?=A2?=82®=C2ù applies only to
DISTINCT VALUES;
well, by definition the index is based on distinct values, so
that?=A2?=82®=E2ÑĘs
kind
of expected.

but B-Tree can be faster for sorting,
again, by definition the index is based on the sorting order, so
that?=A2?=82®=E2ÑĘs
expected too.

the example I use in my training courses is,

b-tree: the table of contents at the front of a book
cluster: the subject index (concordance) at the back of a book

miyako

On 2014/05/02 5:54, "Kirk Brooks" <lists.kirk@... wrote:

color><param>00000,0000,DDEE/param>AArnaud,
I recall Josh talking about indices at the last Summit, I think - I
could
have heard it somewhere else - and saying that it appeared Cluster
indexes
were always the fastest with 4D. This is apparently so even when you
have
unique values. I think my eyes glazed over a bit at the explanation but
that was my take away.
/color>

Reply to this message

Summary created 5/1/14 at 10:16PM by Intellex Corporation

Comments welcome at: feedback@intellexcorp.com