This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:autocomplete:sql [2011/01/24 22:00] els |
admin:indexing:autocomplete:sql [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 23: | Line 23: | ||
| === Initializing the Pull-Down Box === | === Initializing the Pull-Down Box === | ||
| - | The following SQL statement provides the results shown in the previous example. | + | 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 sql> | + | <code> |
| select FNAME, count(*) | select FNAME, count(*) | ||
| from LIST | from LIST | ||
| Line 31: | Line 31: | ||
| group by FNAME | group by FNAME | ||
| order by 2 desc | order by 2 desc | ||
| - | with optimization=distinct_key | + | with optimization=distinct_key; |
| </code> | </code> | ||
| Line 73: | Line 73: | ||
| 32 rows returned | 32 rows returned | ||
| </code> | </code> | ||
| - | + | ||
| - | 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. | + | |
| Line 83: | Line 82: | ||
| 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. | 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 | ||
| + | 6 rows returned | ||
| + | </code> | ||
| ===== ===== | ===== ===== | ||