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