Database Indexing

An Execution Plan are the steps the database needs to perform to execute a query, and for that there needs to be an appropriate access to the data. Most databases use index only. An index is an ordered representation of the indexed data. An index and a query are related and always have to go together when handling large databases. We need to check the access type, and define and order indexes, in order to enhance database performance when handling queries.

Check the Access Type

Using mysql in command prompt, you can find the type of the field (const). The type is also known as access type.

MariaDB [DbName]> explain select * from tableName where ColumnName=1 \G;

The above command will give the following results:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tableName
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra:
1 row in set (0.000 sec)

Access Types

CONST/EQ_REF

• Database performs a B-Tree traversal to find a single value (binary search)
• Can only get used if uniqueness is guaranteed (super fast) when there is one result.
• This can be used if uniqueness (one result) is guaranteed which can de done by (a) a primary key on the column, or (b) a unique constraint.
• Using LIMIT does not guarantee uniqueness.

REF/RANGE

• Known as Index Range Scan
• Performs a B-Tree traversal to find the starting point of a range, then they scan values from that point on) instead of finding a single value.
• Limits the total number of row the database has to inspect to perform a query.
• Stops if it finds the value that does not meet the criteria.

INDEX

• Also known as Full Index Scan
• Starts at the first leaf node and traverses thought the entire index (without filtering).

ALL

• Also known as a Full Table Scan
• Does not use an index at all
• Loads every column of every row from the table.
• Scans through all of them and emits or discards accordingly.

Conclusion

For developers, indexing is a main concern to increase performance and reduce the time to execute a query. It is up to the developer to determine the use of indexes for queries based on use. The choice of using indexes for generating a report from a database is totally different from indexes used to handle daily or even hourly queries. It is about priorities.