Like most database systems, the Radian engine uses indexes to improve performance. An index is a data structure that provides greater efficiency and speed for many database operations. The performance gain from using an index is so great that Manifold requires tables to have an index (normally a btree or btreenull index) to be editable and to support Manifold's interactive selection capabilities (a different thing than using SELECT in SQL).
The default index created by Manifold is a btree index, a classic balanced tree, B-tree, data structure which utilizes a single field in which there can be no NULLs and no duplicate values. Indexes can also be created using more than one field. There are four forms of the basic btree index, referred to collectively as BTREExxx indexes, which cover the four possibilities for whether NULLs are allowed or not allowed and whether duplicates are allowed or not allowed.
Manifold uses rtree spatial indexes for spatial work. Rtree indexes are balanced tree structures utilizing bounding rectangles, the R in rtree. In general, if a drawing's table does not have an rtree index on the geometry field the drawing will not be able to display the objects.
btree |
A classic B-tree, that is a balanced tree, index. A btree index allows no duplicates and no NULLs. |
btreedup |
A B-tree index that allows duplicates but does not allow NULLs. |
btreedupnull |
A B-tree index that allows duplicates and also allows NULLs. |
btreenull |
A B-tree index that allows NULLs but does not allow duplicates; that is, it requires all non-NULLs to be unique. |
rtree |
A spatial index that is a balanced tree structure utilizing bounding rectangles or boxes. |
Whenever possible, indexes from input tables are exposed, that is, available for use in subsequent steps, in the results tables of various SQL statements. This makes writeback possible, that is, the editing of records by editing results tables and it improves performance in many ways:
SELECT exposes indexes on output fields from the underlying table. This provides much better performance in GROUP and ORDER using these indexes.
Replicating an indexed field in the SELECT list exposes multiple copies of the same index.
WHERE optimizes searches via indexes exposed by SELECT, by combining search criteria with the WHERE condition.
JOIN exposes indexes on output fields from the underlying tables. A LEFT outer join loses BTREExxx indexes on the right side. A RIGHT outer join loses BTREExxx indexes on the left side. A FULL outer join loses them on both sides.
The types of BTREExxx indexes exposed by a join can change depending on the join condition: For example, a BTREE index can become a BTREEDUP index if the joined table can include multiple copies of a record from the indexed table. RTREE indexes survive intact in all cases.
ORDER retains the index it uses to order whenever possible.
SELECT allows writing to the underlying table. The table exposed by SELECT must have a BTREE or BTREENULL index. If a particular field from the underlying table participates in the SELECT list twice, it becomes read-only.
JOIN allows writing to the underlying tables when the table exposed by JOIN has a BTREE or BTREENULL index. Attempting to edit at the same time fields within both joined tables will fail.
Editable Results Tables - In most other database systems we normally think of results tables produced by an SQL query as read only, but as can be seen from the list above, in Manifold results tables are often editable. If the results table includes an indexed field, as is often the case with a SELECT or a JOIN statement, we can edit that results table and the edits made to field values automatically will be written back into the source tables. See the Editable Results Tables topic for details.
More than one field in an index - BTREExxx indexes can be built on more than one field. All BTREExxx indexes built on more than one field support searches by partial key.
Partial keys, indexes and WHERE - WHERE optimizes for conditions which engage BTREExxx indexes via a partial key. For example, SELECT ... WHERE a = ... runs faster if the source table has a BTREExxx index with a composite key built on a and other fields, with a coming first in the composite key.
RTREE indexes and spatial searches - RTREE indexes can use a secondary box as a hint for spatial searches, when the query engine exposes an RTREE index on a join that already filters by a spatial criteria.
Selection or SELECT? Manifold allows interactive selection of records in a table using point and click commands or using the Select dialog. That is a different thing than the use of the SELECT statement in SQL. See the Selection topic. SELECT works with any table whether or not there are indexes in the table. Selection requires the table to have an index.
Indexes or indices? Both are correct in English language as the plural form of index, with indexes more popular in the US. Computer people tend to use indexes, presumably due to the influence of Americanisms within computing. Given this documentation was written by many hands, some under the influence of American English and others influenced by UK English, although this documentation uses indexes as the default standard some instances of indices may also appear.
Command Window - Query Builder
Example: Create a Table with a Constraint - Create a simple table that includes a simple constraint upon one of its fields.
Adding an Index to a Table - A basic topic on adding an index using the built-in capabilities of the mfd_id field.
Example: Add a Spatial Index to a Table - A typical use of an index is to provide a spatial index on a geom field in a table, so the geom data can be visualized in a drawing. This topic provides the step by step procedure for adding a spatial index.
Example: Add a UUID-based Index to a Table - Create a new computed field that is filled with UUID values on creation and then create an index on that field. This technique creates an indexed field that has guaranteed unique values for all records and thus the indexed field and record values may be used in other projects.