Overview | Example | SQL | Optimization | Tips

The SQL behind AutoComplete is a simple query requesting grouped counts. For QuickText indexes, it is necessary to use an optimization setting so that one value is returned for each parsed word, rather than one value for each row.

The following SQL statement provides the results shown in the previous example:

  SELECT        FNAME, COUNT(*) 
    FROM        LIST 
    WHERE       FNAME='Wil*' 
    GROUP BY    FNAME 
    ORDER BY    2 DESC 
    WITH        optimization=distinct_key 

This SQL statement will provide results that can then be placed into drop-down boxes as needed:

As the user types more letters, the contents of the pull-down box need to be updated. There are two approaches that can be taken. The recommended approach is to retain the contents of the initial query in memory, and then filter and redisplay them as the user types letters. This is generally much faster than re-querying the database and reduces the load on system resources.

It is also possible to resubmit the query to the database with an additional letter added to the criteria, though care should be given to insure that performance does not degrade with this approach.

Prev | Next

See also: