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. 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.

  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

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.

As the user provides criteria to various fields, that criteria can also be included in the AutoComplete query. For example, the same statement above can be performed with criteria against the State and Last Name columns as shown below:

  SELECT        FNAME, COUNT(*) 
    FROM        LIST 
    WHERE       STATE = 'NY' AND 
                LNAME = 'Myers' AND 
                FNAME='Wil*' 
    GROUP BY    FNAME 
    ORDER BY    2 DESC 
    WITH        optimization=distinct_key;

The AutoComplete choices and their counts will be correspondingly reduced to those in the State of New York with a Last Name of Myers:

FNAME                     COUNT('*')
------------------------  ----------
WILLIAM                            6
WILDA                              1
WILLARD                            1
WILLIE                             1
WILMA                              1
WILMER                             1
6 rows returned

Prev | Next

See also: