Hi Svein

Please accept my apologies if my writeup was misleading. the search term 'invoice' was only an example SQL. In terms of keywords, the end user will be performing searches, not just on 'invoice', but on anything and everything.

I've never tried this before. But I am wondering, if I can start the primary key at a max value, and get the generator to create ID's in a decrementing order, whether that would work. It seems a bit whacky but I might give that a go.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> >hi, thanks for any help with this...
> >
> >i need to select the first x records from a view ordered by ID descending.
> >
> >when the view does not contain an ORDER BY statement then the query is rapid. however when i put an ORDER BY statement
> >in then the query becomes extremely slow (from 1 sec to nearly 1 min).
> >
> >i tried to add a descending index on the docs_search_terms table then recomputed the indexes afterwards with little effect.
> >
> >i will paste the SQL and the plan below. i would be grateful for any assistance with this. thanks.
> >
> >Example Sql
> >---
> >
> >select first 500 * from VW_DOCS_BY_SEARCH_TERMS_LIST where terms containing 'invoice';
>
> I think the keyword here is CONTAINING. CONTAINING will not normally use an index. If 'invoice' is a common word in terms, it might not take long to find 500, but when ORDER BY is added, Firebird first have to find all possible records and then select the first 500 and this might take a while if your tables are big.
>
> There are several possible options for you to make things faster, the first one I've never tried and don't know whether works or not:
>
> create descending index ix_ContainingInvoice on VW_DOCS_BY_SEARCH_TERMS_LIST
> computed by (case when terms containing 'invoice' then ID else -ID end);
>
> with tmp as
> (select * from VW_DOCS_BY_SEARCH_TERMS_LIST ORDER BY case when terms containing 'invoice' then ID else -ID end DESC)
> select * from tmp
> where terms containing 'invoice'
>
> The second option is using EXECUTE BLOCK, traverse through all records in docs_search_terms in descending order and return the first 500 that matches your criteria. This should be quicker if 'invoice' commonly occurs in your table.
>
> The third option is to make one or two more tables that contains WORDS and a link to the record where it occurs. That way you could change your query from using CONTAINS to using equality.
>
> The fourth and preferred option if possible, is to replace CONTAINING with STARTING WITH. Though I assume this is not an option in your case.
>
> There are probably other options as well, the important lesson for you regarding Firebird is that CONTAINING is not a good option if you want indexes to be used.
>
> HTH,
> Set
>