Database Indexes for database optimization

If you are looking into troubleshooting the performance of a database and the queries that run on it, you can make use of the database feature, database indexes. This feature if used efficiently, by ensuring indexes are configured and being used correctly in your queries, may help in improving query response times and avoid any unnecessary use of more expensive newer hardware.

Database table example:

Table name: customers

Name Type Size Description
customer_id char 10 NOT NULL
name varchar 40
surname varchar 40
phone_number int 11

To find the customer Dave’s phone number with the customer_id = ‘274178’, you would need to:

  1. Run a SQL query
  2. SELECT phone_number FROM customers WHERE customer_id = ‘274178’
  3. To find the correct record MySQL would have to search through all the records in the entire table i.e. possibly searching through thousands of entries.

Database Indexes work in the same way as a book index. The index helps the reader find the relevant information quickly by searching the index i.e. instead of going through each page of the book until the correct information is found. Tables may have multiple indexes applied to them. Each index may reference one or more columns. The best way to see where indexes will provide benefit is to see which columns are referenced in the “WHERE” portion of the query.

Explain command:

EXPLAIN is a command that provides some insight into the methods MySQL will use to optimise a query and its output can be a useful tool to identify and remove obstacles. Using EXPLAIN is as simple as adding the keyword EXPLAIN at the start of a query.

Example: The following will demonstrate the ‘explain command’ by investigating the table above:

EXPLAIN SELECT customer_id,name,surname FROM customers WHERE customer_id = ‘274178’;

table type possible_keys key key_len ref rows Extra
CUSTOMERS ALL NULL NULL NULL 283211 2 where used

Looking at the possible_keys the output is null, which indicates there are no Indexes currently and that MySQL has to search 283211 records to return the result.

Create index example:

CREATE INDEX indx_customer_id ON customer (customer_id);

If we re-run the EXPLAIN the output changes to:

table type possible_keys key key_len ref rows Extra
customers const PRIMARY PRIMARY 10 const 1

Comparing the output to the previous explain output, you’ll notice that the possible_keys field now says PRIMARY and only had to search 1 row to find the record.

Note: Indices can be on any column on the table

For additional information about database indexing and database optimizing refer to the following:

Related Posts: