Thank you for replying Adam. Some questions and more info below.

Adam wrote:

> --- In firebird-support@yahoogroups.com, Kjell Rilbe <kjell.rilbe@...>
> wrote:
>
>>Please remind me, what is the max key size in FIrebird 1.5?
>>
>>I just failed to create a compound index on these two columns:
>>- int
>>- varchar(200)
>
> I believe it is around 252 bytes. Is this a multi-byte charset?

No, it's WIN1252, and as far as I know WIN1252 is 8-bit. So I guess that
index should work then?

>>What I actually need is to make the following queries as fast as
>>possible:
>
> You don't tell us which of these fields is int and which is varchar,
> but guessing at the obvious (Info varchar(200), Code Integer).

Correct guess. Sorry i didn't write it.

>>UPDATE "Master"
>>SET "Info" = :Caption
>>WHERE "Code" = :Code;
>
> You will want an index either on Code or on Code combined with some
> other field in a superkey.

Yes. Rather obvious... :-)

>>UPDATE "Master" M
>>SET "Info" = "Info" || '; ' || :Caption
>>WHERE EXISTS (
>> SELECT 1
>> FROM "Detail" D
>> WHERE D."Id" = M."Id"
>> AND D."Code" = :Code
>> )
>> AND M."Info" IS NOT NULL;
>>
>>UPDATE "Master" M
>>SET "Info" = :Caption
>>WHERE EXISTS (
>> SELECT 1
>> FROM "Detail" D
>> WHERE D."Id" = M."Id"
>> AND D."Code" = :Code
>> )
>> AND M."Info" IS NULL;
>>
>>"Master" ("Id") is primary key.
>>
>>"Detail" ("Id", "Code") should be primary key but I just noticed
>>that it isn't.
>
> Hmmm, that wont work. Code/Detail is the same as Detail/Code in
> uniqueness. Judging from the selectivity of Detail(Code, ID) below,
> you have duplicates and lots of them. Unless you mean that it should
> be an ascending index??

What do you mean "wont work"? There are no duplicates in ("Id", "Code").

>>"Detail" ("Code", "Id") has a non-unique ascending index (selectivity
>>0.002433).
>>
>>"Detail" contains up to about five records for each "Master".
>
> IIRC, statistics is pretty much = (distinct values / total values)
>
> Unless I am reading your selectivity wrong, shouldn't it be around 0.2
> if this was the case? It is I suppose possible your statistics are way
> out of date. Run set statistics to see if it changes.

Possible that the stats are out of date, but the figure doesn't seem
unreasonable considering there are UP TO five details per master. There
are actually rather a lot of masters with no detail at all.

So, what index SHOULD I have to make those last two queries run fast?
("Id", "Code"), ("Code", "Id"), ("Code") or ("Id")? I could just test
them all and see what performance I get...

Thanks,
Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64