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:

FNAME                     COUNT('*')
------------------------------------
WILLIAM                       118130
WILLIE                         19307
WILMA                           5075
WILLARD                         2350
WILLIS                          1661
WILBUR                          1660
WILBERT                         1357
WILSON                          1351
WILFRED                         1141
WILL                             826
WILLA                            800
WILFREDO                         698
WILLIAMS                         626
WILEY                            536
WILDA                            457
WILFORD                          428
WILMER                           367
WILHELMINA                       348
WILBURN                          345
WILTON                           280
WILLY                            216
WILBER                           175
WILLENE                          163
WILLIAN                          155
WILLIA                           113
WILLETTE                         104
WILLIEMAE                         59
WILLODEAN                         48
WILLENA                           47
WILHEMINA                         43
WILLOW                            39
WILLETTA                          35
32 rows returned

Depending on the size of the database, it may be important to not issue a SQL statement until the user has typed two or three letters. That can dramatically affect the number of rows retrieved, and also the time required to query the Omnidex indexes.

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: