MySQL 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 MySQL feature, MySQL 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

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

MySQL 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’;

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:

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