|Subject||Re: Speedy HTML list boxes|
> I think the substring idea is a great one.name,
> It took me a little while to understand what you were having me do with
> the substring search table. You're suggesting I take the customer
> and break it up into it's searchable parts, and store each part (with aindexed
> duplicate id number) in the search table. This way a high-speed
> search can find a subgroup of id's - with the tradeoff of higherThat is the normal way of doing it in Firebird. Obviously an index can
> insert/update times (not a concern for me for this) and increased disk
> usage (also not a concern right now).
only help a "containing" query if it can be transformed to a "Starting
When the number of customers gets large, a fetch all query will take
an awful long time to return and there will be some UI design issues
as well. If you restrict it with the where clause, the execution time
of the query will be just as slow because it can't use an index to
narrow down the results, but at least the fetch time is going to be
So the suggestion is to have a generic words table, a customer table,
and a table linking customers and words.
1, The Firebird Foundation
Obviously, you will want to store words in upper case or something,
and you can use a query similar to this:
select distinct c.ID, c.Name
from Words w
join CustomerWords cw on (w.ID = cw.WordsID)
join Customer c on (cw.CustomerID = c.ID)
where w.Name containing 'F%'
I would also recommend using a first n syntax, if they happen to type
"International" etc as a keyword, they may get a lot of companies,
there is no point showing them all.
This method is not specific to HTML, any interface needs to deal with
this issue. Specifically using html, if you have the option to use
to run the query with the current keywords.