This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
admin:features:rollups:indexing [2012/02/17 16:37] doc |
admin:features:rollups:indexing [2016/06/28 22:38] (current) |
||
---|---|---|---|
Line 14: | Line 14: | ||
- | Once the rollup tables have been created and declared, they may be indexed using Omnidex indexing. There are no restrictions on indexing of rollup tables, and rollup tables can be indexed just like any other table. Typically, they would be installed with both Omnidex MDK and Aggregation indexes. | + | Once the rollup tables have been created and declared, they may be indexed using Omnidex indexing. There are no restrictions on indexing of rollup tables, and rollup tables can be indexed just like any other table. |
Since rollup tables are often quite small, it is common to heavily index the rollup table. Ultimately, it is only necessary to index the rollup table in such a way that all queries that are redirected to the rollup table are fully optimized. This can be determined by reviewing the query plan for each query that is redirected to the rollup table. This is discussed in more detail later in this document. | Since rollup tables are often quite small, it is common to heavily index the rollup table. Ultimately, it is only necessary to index the rollup table in such a way that all queries that are redirected to the rollup table are fully optimized. This can be determined by reviewing the query plan for each query that is redirected to the rollup table. This is discussed in more detail later in this document. | ||
- | <code sql> | + | The following example shows indexing for the tables that were created in the previous page. |
+ | |||
+ | <code> | ||
create table "INDIVIDUALS" | create table "INDIVIDUALS" | ||
physical "dat/individuals.dat" | physical "dat/individuals.dat" | ||
Line 73: | Line 75: | ||
</code> | </code> | ||
+ | Indexes on rollup tables are automatically updated when the UPDATE INDEXES statement is issued: | ||
+ | |||
+ | <code> | ||
+ | > update indexes | ||
+ | Database | ||
+ | Table Rows CPU Elapsed | ||
+ | ---------------------------------------------------------------------------- | ||
+ | SIMPLE | ||
+ | COUNTRIES 239 0:00 0:00 | ||
+ | STATES 76 0:00 0:00 | ||
+ | GENDERS 2 0:00 0:00 | ||
+ | HOUSEHOLDS 1,909 0:00 0:00 | ||
+ | INDIVIDUALS 5,000 0:00 0:00 | ||
+ | INDIVIDUALS_BY_HOUSEHOLD 5,000 0:00 0:00 | ||
+ | INDIVIDUALS_BY_DEMO 4,793 0:00 0:00 | ||
+ | ---------------------------------------------------------------------------- | ||
+ | Total 0:01 0:03 | ||
+ | |||
+ | Omnidex indexes updated | ||
+ | > | ||
+ | </code> | ||
==== ==== | ==== ==== | ||
**[[admin:features:rollups:create|Prev]]** | **[[admin:features:rollups:optimization|Next]]** | **[[admin:features:rollups:create|Prev]]** | **[[admin:features:rollups:optimization|Next]]** | ||
+ | |||
====== Additional Resources ====== | ====== Additional Resources ====== |