Subject Re: Speedy HTML list boxes
Author Adam
> I think the substring idea is a great one.
> 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 a
> duplicate id number) in the search table. This way a high-speed
> search can find a subgroup of id's - with the tradeoff of higher
> insert/update times (not a concern for me for this) and increased disk
> usage (also not a concern right now).

That is the normal way of doing it in Firebird. Obviously an index can
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.


ID, Name
1, The Firebird Foundation
2, IBPhoenix

ID, Name
1, The
2, Firebird
3, Foundation
4, IBPhoenix

CustomerID, WordsID


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
javascript etc, you could have some key combination Ctrl + something
to run the query with the current keywords.