--- 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?

>
> 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).

>
> 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.

> 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??

>
> "Detail" ("Code", "Id") has a non-unique ascending index (selectivity
> 0.002433).
>
> "Detail" contains up to about five record 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.

Adam