Adding an Index to a Table

To be editable or selectable, tables in Manifold must have at least one btree or similar index.  That is usually done automatically for us, but sometimes we manually must add an index to a table.  This topic shows how.

 

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: using them is slower, we cannot edit them and we cannot make selections in them. That is true of most database systems, including Manifold.  To enable editing or selection in tables, we must add an index to them.

 

Adding an index to a table:

 

  1. Double-click on the table to open it.

  2. Choose Edit - Schema.

  3. In the Schema list, click the <new field> item.

  4. In the Field box specify mfd_id as the name of the field.

  5. In the Type box choose int64.

  6. Press the Add button.  A new field appears in the Schema list.

  7. In the Schema list, click the <new index> item.

  8. In the Index box specify mfd_id_x as the name of the index.

  9. Press the Add button.  A new index appears in the Schema list.

  10. In the Schema list under the new mfd_id_x index, click the <new field> item.

  11. In the Field box choose mfd_id from the pull-down list.   Press Add.

  12. The mfd_id field appears as a new key field under the index.

  13. Press OK.

 

The procedure for adding an index sounds lengthy when every small step is enumerated, but it boils down to three parts: a) add a new field that will be used as a key field for the index, b) add a new index, and then c) tell the index what key field it should use.

Automatic Creation of Indexes

Manifold automatically creates an index as necessary when importing tables into a project from many spatial data sources used in GIS work, for example, when importing a drawing.   Most Manifold dataports that import data will automatically add an mfd_id key field and an mfd_id_x index to tables.   When we create a new, blank table in a Manifold project, Manifold will by default create that table with an mfd_id key field and an mfd_id_x index.   

 

When we import data from sophisticated non-spatial data formats, such as a database, we usually get a table with an index as well, since DBMS people routinely will include indices for performance in their native database systems.  Manifold of course will import and will utilize those indices along with the rest of the schema and data.   

 

But if we import a table from a simple format like CSV that does not allow indexes, then Manifold will not automatically add an index, in respect of keeping the data exactly as is in the original form.  In that case when we pop open the table we will see it with gray background color that indicates it cannot be edited.   

 

To make such tables editable we manually will add at least one btree index to the table.   That will improve overall performance and also will enable interactive selection and editing within table windows.    We can add an index to an existing field in the table, if there is a suitable such field, or we can add a new key field to be used by the new index.

 

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 in at least 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 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.   The following example shows how to do that step by step.

Using an mfd_id Field for an Index

The field name mfd_id is a special name that is supported by special Manifold infrastructure.   A field called mfd_id will be automatically populated by Manifold with unique values, as needed, 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.  

 

We can reorder, show/hide and resize the columns in the table by using the Layers panel in the Contents pane.

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.

 

Alternatives to mfd_id -  Manifold's special treatment of an mfd_id field is very useful, but it is an example of applied magic unique to Manifold.   Sometimes we might want to add a key field to a table to be used for an index which does not in any way rely on special features built into Manifold.   A 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.

 

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.