Differences

This shows you the differences between two versions of the page.

--- admin:indexing:autocomplete:sql [2011/01/24 15:29]
127.0.0.1 external edit
+++ admin:indexing:autocomplete:sql [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
 ~~NOTOC~~
 {{page>:top_add&nofooter&noeditbtn}}
@@ Line 18: / Line 19: @@
 ==== The SQL Behind AutoComplete ====
+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.
+=== Initializing the Pull-Down Box ===
+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.
+<code>
+  select        FNAME, count(*)
+    from        LIST
+    where       FNAME='Wil*'
+    group by    FNAME
+    order by    2 desc
+    with        optimization=distinct_key;
+</code>
+This SQL statement will provide results that can then be placed into drop-down boxes as needed:
+<code>
+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
+rows returned
+</code>
+=== Refining the Pull-Down Box ===
+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.
+=== Subsequent Searches ===
+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:
+<code>
+  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;
+</code>
+The AutoComplete choices and their counts will be correspondingly reduced to those in the State of New York with a Last Name of Myers:
+<code>
+FNAME                     COUNT('*')
+------------------------  ----------
+WILLIAM                            6
+WILDA                              1
+WILLARD                            1
+WILLIE                             1
+WILMA                              1
+WILMER                             1
+rows returned
+</code>
 =====  =====

Back to top

admin/indexing/autocomplete/sql.1295882947.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes