Creating and Using Indexes

OpenBase offers 5 different types of indexes using SQL syntax and one index type using stored procedures (described in the stored procedure section). Indexes are used to speed up searching and are particularly necessary when joining tables. We recommend that columns used in searching or as foreign keys and primary keys get indexed. This section describes how to create indexes using SQL.

Here are a few examples:

CREATE INDEX tablename column
CREATE UNIQUE INDEX tablename pkcolumn

Here is an example using the pubs database:

CREATE INDEX authors au_fname

Here is a list of the various types of indexes you can use:

UNIQUE INDEX — this offers fast access and makes sure that all records in the column that is indexed are unique. A unique index is always required when generating primary keys for the column.

Here is an example of creating a unique index on the authors table in the pubs database:

CREATE UNIQUE INDEX authors au_ID

INDEX — this offers a non-unique index, allowing multiple records to have the same value.

CREATE INDEX authors au_fname

CLUSTERED INDEX or CLUSTERED UNIQUE INDEX — this option can be placed on one INDEX or UNIQUE INDEX for each table. It orders the data in the table by the index to improve performance. Generally, columns which are used first in an ORDER BY are good candidates for a clustered index. It also works well on foreign keys where a group of records are often returned by a single foreign key value.

Here is an example of creating a clustered index:

CREATE CLUSTERED INDEX authors au_fname

WORD INDEX — this is the index type used on OBJECT and TEXT type columns. A WORD index breaks up the actual data into words and indexes the first 15 characters of every word. It only works with the LIKE operator and it does change the behavior of LIKE slightly.

Since OBJECT and TEXT columns are BLOBs they only support word indexes. A word index is assigned the same way a regular index is defined, with the keyword INDEX.

If you have a lot of blobs or are doing complex searches, you should also take a look at the Lucine stored procedures for maintaining indexes. This offers a faster index mechanism that is better suited for larger data sets than a word index.

Special Rules for Searching BLOB Content
OpenBase allows you to search WORD indexes on TEXT and OBJECT types using the LIKE operator. While it is customary to use the normal wildcards, there are some restrictions. You can search for the following:

Words that are less than 20 characters in length, including underscore, single-quote and dash.

  • International characters defined in the sort language are also included.
  • Punctuation is omitted and not searchable.
  • Your TEXT or Object must contain ASCII data. A PDF can be searchable as long as it contains unbroken text inside it which allows the parser to index words with boundaries.

Furthermore, there are some additional rules for searching. By default, a search of column LIKE '*Apple* ' will only return documents that contains the word Apple. This is a case insensitive search.

If you want to look for all words that start with App, including Apple, you can specify this using a double star. For instance,

column LIKE '*App**' .

If you need to do an exhaustive (slow) search on BLOBs which follow more traditional rules for LIKE, then you can specify a double star at the beginning of the value. For instance,

column LIKE '**ppl**'.

Joins using multiple columns

In some cases an existing design may use two values to define a relationship between two tables. While this can technically be done, it will guarantee poor performance as your database grows.

Here is an example of what we are talking about:

SELECT * FROM table1, table2 where table1.orderdate = table2.orderdate and table1.order_no = table2.order_no

If you need to join two tables by two values, we recommend creating a derived column. This is how we would fix the above example:

ALTER TABLE table1 ADD COLUMN date_order_no VARCHAR INDEX DERIVED orderdate + ':' + order_no
ALTER TABLE table2 ADD COLUMN date_order_no VARCHAR INDEX DERIVED orderdate + ':' + order_no

Here is how we would then query the table:

SELECT * FROM table1, table2 where table1.date_order_no = table2.date_order_no

This will provide significantly better performance.

Multi-column primary keys

A topic that is similar to joining with multiple columns is multi-column primary keys. While they will work, it is always going to give you significantly better performance to access tables by a single value.

If you have multiple columns that make up the key, there is a way to combine them for searching with little impact to your software. Here is the example from the last section on combining the two columns into one for searching:

ALTER TABLE table1 ADD COLUMN date_order_no VARCHAR INDEX DERIVED orderdate + ':' + order_no

Here is an example of how to search:

SELECT * FROM table1 WHERE date_order_no = "2009-11-22 14:18:49 -0500:66272"

You can also use the LIKE operator to search for records that match one part:

SELECT * FROM table1 WHERE date_order_no LIKE "*:66272"

or

SELECT * FROM table1 WHERE date_order_no LIKE "2009-11-22 14:18:49 -0500:*"
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License