Adding an Index to a Table

In most cases when we bring a table into Manifold from some outside source it will either have an index in the table or Manifold will automatically create one for us.  Tables without indexes are inconvenient: we cannot edit them and we cannot make selections in them, a typical situation in most DBMS environments, including Manifold.   

 

To reduce such inconveniences, when we import a table or create it normally we will create it with at least one btree index to facilitate performance overall as well as interactive selection and editing within table windows.    Most Manifold dataports that import data will automatically add an mfd_id field and mfd_id_x index for imported data.

 

However, sometimes we may need to work with a table that both has no index and also has no existing field that is convenient to use for an index.   For example, if all of the fields in a table have NULL values for some records none of the fields will be suitable for use as an index.  We will have to add a new field to the table and then create an index on it.

 

The easiest way to do that when a table will be used within the same project is to exploit Manifold's built-in, special understanding of a field named mfd_id to support creation of an index.   We create a field called mfd_id and then we create an index called mfd_id_x on that field.   This example shows how to do that step by step.

 

A slightly more complex but more general way to create an index for a table is to create a new, computed field that is populated upon creation with UUID values.  That allows using tables with such indexes in other projects on other machines because the field used for the table's index is guaranteed to always have unique values.   That procedure is described step by step in the Example: Add a UUID-based Index to a Table topic.

Using an mfd_id Field for an Index

Manifold utilizes the built-in capability that a field called mfd_id will be automatically populated with unique values, so it may be used as the basis of an index.  This capability is specific to Manifold but it is very convenient when we work with tables within Manifold.

 

eg_add_index02_01.png

 

Consider the above table, which we have imported from a non-DBMS format that provides no indexes.   As imported the table has no index so the light gray background indicates that the field values cannot be edited and records cannot be selected.   To allow either selection or edits we must add a btree index.

 

If the table had a field with unique, non-NULL values already in it we could use that field to create an index.  But this table contains only two fields and both of those fields have either NULL values or duplicates for some records, which means neither of the two fields can be used to create an index.  We must add a new field that contains unique, non-NULL values and only then can we use it to create an index.  

 

We choose Edit - Schema to launch the Schema dialog.   We will add a new field called mfd_id to the table and then create a btree index on that field.

 

eg_add_index02_02.png

 

We click on <new field> to start adding a new field.

 

eg_add_index02_03.png

 

For the name of the field we enter mfd_id into the Field box.  We must now select the Type of the field from the lengthy list of data types in the pull-down menu.

 

eg_add_index02_04.png

 

We choose int64 as the Type.   We click the Add button to create the new field in the table's schema.  tech_ravi_sm.png

 

Tech Tip:  Choosing int64 as the type for our new mfd_id field is a habit that ensures we will never run out of unique values for that field since 64-bit integers allow for over a quintillion records.   Given that 32-bit integers allow over two billion unique values (and records)  we could have used int32 as the Type for our new mfd_id field since we probably are not going to have more than two billion records in our table.   But given the growth rate in data that we might use and the increasing speed of computers it could well be that we might find ourselves working with tables that have more than two billion records and thus wishing we had used int64 as the Type.   Manifold always creates mfd_id fields as int64 when they are automatically created.  Most Manifold users will also "future proof" their indices when manually creating mfd_id fields by also creating them as int64 fields.

 

We now will add a new index, which we will call mfd_id_x, to the table.

 

eg_add_index02_05.png

 

We click on <new index>.

 

eg_add_index02_06.png

 

We enter mfd_id_x into the Index box and leave the default btree choice in the Type box.   Cllick the Add button to create the new index in the table's schema.  

 

We could call the new index whatever we want.   By convention within Manifold we name the index by appending _x  to the name of the field that will be used for the index.  That is a convenient tradition that helps make complicated schemas easier to understand and it makes it possible for all Manifold users to see at a glance which parts of a schema are indexes and what fields they use.

 

We will now specify which field within the table will be used for the index.

 

eg_add_index02_07.png

 

We click on <new field> in the hierarchy under mfd_id_x and then pull down the menu for the Field box that contains a list of available fields in the schema.   

 

eg_add_index02_08.png

 

We choose mfd_id from the Field drop down list.

 

eg_add_index02_09.png

 

We click the Add button to add that field for use by the index.

 

eg_add_index02_10.png

 

We have created a new mfd_id field of type int64 and we have created a btree index called mfd_id_x on that new field.   If we press Cancel the changes to the schema will be abandoned.  We press OK to apply the changes in the schema to the table.

eg_add_index02_11.png

 

The table now has a new mfd_id field which is automatically populated by Manifold with unique, non-NULL  values.  Because the table now has an index the background of the table has changed to white, indicating that it may be edited and that interactive selection is enabled.  

 

Notes

Why special treatment of mfd_id?   Manifold is often used with spatial data that originated in GIS or other systems which do not take as rigorous an approach to database matters as do enterprise class DBMS applications.   It cannot be taken for granted that such data has indexes and much of it usually does not.  To make it easier to apply more rigorous DBMS discipline within Manifold with such data the system makes use of a special case mfd_id field to provide a unique identifier for records and to always automatically build an index on mfd_id called mfd_id_x.   This makes it convenient always to have an automatically-created unique identifier for records and an index on that identifier for data that is brought into Manifold from GIS formats and data sources that do not have indexes, but it does mean that to avoid collisions from attempting to create duplicate values of mfd_id we should use a different name in JOINs and similar.  For example, use SELECT [mfd_id] AS mfd_id1... and not simply SELECT [mfd_id].  A mfd_id value is unique per table, but not between different tables in the same project, and not between tables in different projects.

 

See Also

Tables

 

Data Types

 

Indexes

 

Schema

 

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.