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.

Index Types


A classic B-tree, that is a balanced tree, index.  A btree index allows no duplicates and no NULLs.


A B-tree index that allows duplicates but does not allow NULLs.


A B-tree index that allows duplicates and also allows NULLs.


A B-tree index that allows NULLs but does not allow duplicates; that is, it requires all non-NULLs to be unique.


A spatial index that is a balanced tree structure utilizing bounding rectangles or boxes.


Indexes are Exposed in Results Tables

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:



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.

See Also



Data Types






Command Window


Command Window - Query Builder


SQL Statements


SQL Functions


SQL Operators


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.