This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:activecounts:optimization [2011/01/24 15:29] 127.0.0.1 external edit |
admin:indexing:activecounts:optimization [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ~~NOTOC~~ | ~~NOTOC~~ | ||
| + | |||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 16: | Line 17: | ||
| ==== The Optimization Behind ActiveCounts ==== | ==== The Optimization Behind ActiveCounts ==== | ||
| - | The SQL statements that allow ActiveCounts are fairly ordinary. They are primarily a succession of grouped aggregations, with an additional piece of criteria added at each step. The key to ActiveCounts lies in the optimization of these queries. Omnidex has special optimizations for this particular kind of queries. | + | The SQL statements that allow ActiveCounts are fairly ordinary. They are primarily a succession of grouped aggregations, with an additional piece of criteria added at each step. The key to ActiveCounts lies in the optimization of these queries. Omnidex has special optimizations for this particular kind of query. |
| Omnidex is specifically designed as a count engine. By allowing many indexes to be used together on the same search, and by maintaining a count at each step of the search, it is ideally suited to providing fast counts. As long as Omnidex is set up so that all criteria and aggregations can be satisfied within the indexes, Omnidex will be able to provide fast counts along the way. | Omnidex is specifically designed as a count engine. By allowing many indexes to be used together on the same search, and by maintaining a count at each step of the search, it is ideally suited to providing fast counts. As long as Omnidex is set up so that all criteria and aggregations can be satisfied within the indexes, Omnidex will be able to provide fast counts along the way. | ||
| Line 28: | Line 29: | ||
| For example, if the original SQL statement presents genders and their counts, using state codes as criteria ... | For example, if the original SQL statement presents genders and their counts, using state codes as criteria ... | ||
| - | <code sql> | + | |
| + | <code> | ||
| select GENDER, count(*) | select GENDER, count(*) | ||
| from LIST | from LIST | ||
| where STATE in ('AZ','CA','CO','NM','OR','UT','WA') | where STATE in ('AZ','CA','CO','NM','OR','UT','WA') | ||
| group by GENDER; | group by GENDER; | ||
| + | |||
| </code> | </code> | ||
| ... and then the subsequent SQL statement presents educations and their counts, using state and gender codes as criteria ... | ... and then the subsequent SQL statement presents educations and their counts, using state and gender codes as criteria ... | ||
| - | <code sql> | + | <code> |
| select EDUCATION, count(*) | select EDUCATION, count(*) | ||
| from LIST | from LIST | ||
| Line 44: | Line 48: | ||
| group by EDUCATION; | group by EDUCATION; | ||
| </code> | </code> | ||
| + | |||
| ... Omnidex will recognize that the second query is similar to the first query, except for the addition of new criteria against GENDER. It will recognize that the index pointers from the first search, which represent the Western United States, can be reused. The current query simply requires the addition of the new criteria against GENDER. This saves a great deal of resources. | ... Omnidex will recognize that the second query is similar to the first query, except for the addition of new criteria against GENDER. It will recognize that the index pointers from the first search, which represent the Western United States, can be reused. The current query simply requires the addition of the new criteria against GENDER. This saves a great deal of resources. | ||