This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
admin:indexing:strategies:criteria [2011/04/13 03:32] 127.0.0.1 external edit |
admin:indexing:strategies:criteria [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ~~NOTOC~~ | ~~NOTOC~~ | ||
| + | |||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 23: | Line 24: | ||
| In the following statement, look for the columns used as criteria in the WHERE clause | In the following statement, look for the columns used as criteria in the WHERE clause | ||
| - | <code sql> | + | <code> |
| select HOUSEHOLD, ADDRESS, CITY, STATE, ZIP | select HOUSEHOLD, ADDRESS, CITY, STATE, ZIP | ||
| from HOUSEHOLDS | from HOUSEHOLDS | ||
| Line 29: | Line 31: | ||
| (STATE = 'IL' and CITY = 'Chicago')); | (STATE = 'IL' and CITY = 'Chicago')); | ||
| </code> | </code> | ||
| + | |||
| The STATE and CITY columns should be Omnidex indexes. | The STATE and CITY columns should be Omnidex indexes. | ||
| Line 36: | Line 39: | ||
| In this example, you might want to use QuickText indexes. QuickText indexes will parse and index each word in the column and will allow them to be searched case-insensitively. | In this example, you might want to use QuickText indexes. QuickText indexes will parse and index each word in the column and will allow them to be searched case-insensitively. | ||
| - | <code sql> | + | <code> |
| select INDIVIDUAL, NAME, PHONE | select INDIVIDUAL, NAME, PHONE | ||
| from INDIVIDUALS | from INDIVIDUALS | ||
| where BIRTHDATE = 'Jan 10, 1986' and NAME = 'John'; | where BIRTHDATE = 'Jan 10, 1986' and NAME = 'John'; | ||
| </code> | </code> | ||
| + | |||
| The BIRTHDATE column should be an Omnidex index and the NAME column should be a Quicktext index. | The BIRTHDATE column should be an Omnidex index and the NAME column should be a Quicktext index. | ||
| Line 48: | Line 53: | ||
| If a column has low cardinality, meaning that it has less than 32 distinct values, it should be indexed as an Omnidex Bitmap index. This improves performance and saves disk space. | If a column has low cardinality, meaning that it has less than 32 distinct values, it should be indexed as an Omnidex Bitmap index. This improves performance and saves disk space. | ||
| - | <code sql> | + | <code> |
| select INDIVIDUAL, NAME, PHONE | select INDIVIDUAL, NAME, PHONE | ||
| from INDIVIDUALS | from INDIVIDUALS | ||
| Line 54: | Line 60: | ||
| </code> | </code> | ||
| - | The GENDER column should be an Omnidex Bitmap, and the NAME column should be a QuickText index. | + | |
| + | The GENDER column should be an Omnidex Bitmap index, and the NAME column should be a QuickText index. | ||
| == Sample Environment File == | == Sample Environment File == | ||
| Line 60: | Line 67: | ||
| This sample environment file shows the Omnidex indexes that will optimize these queries. | This sample environment file shows the Omnidex indexes that will optimize these queries. | ||
| - | <code sql> | + | <code> |
| create environment | create environment | ||
| in "simple.xml" | in "simple.xml" | ||