This shows you the differences between two versions of the page.
|
admin:indexing:autocomplete:sql [2011/01/24 21:58] els |
admin:indexing:autocomplete:sql [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Administration: Omnidex Indexing ====== | ||
| - | |||
| - | ===== AutoComplete ===== | ||
| - | |||
| - | [[admin:indexing:autocomplete:home|Overview]] | | ||
| - | [[admin:indexing:autocomplete:example|Example]] | | ||
| - | **[[admin:indexing:autocomplete:sql|SQL]]** | | ||
| - | [[admin:indexing:autocomplete:optimization|Optimization]] | | ||
| - | [[admin:indexing:autocomplete:tips|Tips]] | ||
| - | |||
| - | ---- | ||
| - | |||
| - | |||
| - | |||
| - | ==== 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: | ||
| - | |||
| - | <code sql> | ||
| - | 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 | ||
| - | 32 rows returned | ||
| - | |||
| - | </code> | ||
| - | ^ First Name ^ 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 | | ||
| - | |||
| - | === 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. | ||
| - | |||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[admin:indexing:autocomplete:example|Prev]]** | | ||
| - | **[[admin:indexing:autocomplete:optimization|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||