At 03:48 PM 2/07/2003 +0200, you wrote:

>Helen,
>as usual most of your advice is brilliant, but when it comes to the indexes
>you want him to create, I suggest it is better to create them on the other
>tables (as I already suggested in my first mail, though I errouneously
>forgot to include sub_code in one of them). The reason is that he already
>has a where clause covering the log table and that what we want is for him
>to use one index for his where clause and then one index on each table to
>link to.

Set,
Did you notice that neither of the criteria in the WHERE clause is involved
in the joins?

Did you notice the low selectivity of the index you suggested?

You did notice the fact that there were composite indexes stepping on one
another but you didn't notice the low selectivity of all of them.

You didn't make any recommendations about indexing for the joins as far as
I can tell. But maybe you posted something that I missed...

h.

>Set
>
>At 23:03 02.07.2003 +1000, you wrote:
> >SELECT
> >l.Receipt, l.Receipt2, l.Acc_Num, l.Sub_Code,
> >SUM(l.MQuantity) AS MQuantity, L.INV_TYPE, L.INV_PREFIX,
> >c.SurName, s.Sub_Name, s.Card_Code, s.RegNum
> >FROM log L
> >JOIN client C
> >ON C.code = L.acc_Num <<<==== Index L.acc_num (SET: Indexing C.code would
> >be better)
> >JOIN clnt_sub S
> >ON S.code = L.acc_num <<<=====another index needed
> >AND S.sub_code = L.sub_code over these two columns (SET:
> >preferrably s.code, s.sub_code)
> >WHERE
> >L.POS_ID = :POSID
> >AND L.SHIFTNUM = :SHIFTNUM
> >GROUP BY l.Receipt, l.Receipt2, l.Acc_Num, l.Sub_Code,
> >l.inv_type, l.inv_prefix, c.SurName, s.Sub_Name, s.Card_Code,
> >s.RegNum
> >ORDER BY c.Surname, l.Acc_Num, l.Sub_Code
>
>
>
>To unsubscribe from this group, send an email to:
>firebird-support-unsubscribe@yahoogroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/