Subject Re: [firebird-support] Re: Table with more than 100 index.
Author Alexandre Benson Smith
svanderclock wrote:
>> My first attempt would be to create an index for each individual field.
>> Firebird could combine 2 or more indices to narrow down the result set.
>> So if you have 10 fields, you will need only 10 indices and not every
>> possible combination.
>> The sort will be in memory (as far as you have enough memory of course),
>> a Quick Sort on 10 000 records should be instantaneous.
>> see you !
> this what we do now. but how to detect when a result set will gave you only 10 000 rows and not in some other case 2 000 000 row (or more) ! how to say to firebird : if more than 2 000 000 then don't do the order by ! no way so in this case no choice than use an index in all possible combination ...

If a user supply a filter condition that returns 2 million rows and want
this result ordered, I don't know how you can prevent :)

But, how much time would take to do a "select count(*) from Bla where
SearchCondition" if the SearchCondition is not restrictive enough ?

Another option, Can you try to not order on the server side, and fetch
the results to the client, and if you reach a given limit (for example >
10000), sugest the user to try a more restrictive filter condition, or
have patience to wait for the sorting, if the user insist in return a
LOT of records, he must accept to wait for the sorting on the client side.

see you !

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil