Optimize MySQL Queries with Indexes

Aug 19, 2019 by - Category - Database

I have been recently got stuck with slow SQL performance in one of my project so after searching a lot and with personal experience, I came across few sorted steps that helped me to keep good health of my SQL queries and made it faster. So here I have mentioned the brief from what I researched and what helped me.

Set Profiling: Enabling SQL profiling will keep track of all your queries until you disable it, you can check your queries any time while debugging with mysql>show profiles; command. It will give you all the queries you ran from the time you enabled profiling with information like query_id, duration, and query.

To Enable SQL Profiling 

mysql> SET profiling = 1;

To Check SQL Profiling 

mysql> show profiles;
List of all the queries from the time profiling is ON

To DIsable SQL Profiling

mysql> SET profiling = 0;

So with show profiles;  if you find any query taking so much time then its time to examine that particular query. In our case following query taking more time

Select id from test where description like ‘%qui%’;

Examine the SQL Query: Explain is used to examine your SQL query.  Once you command your SQL Engine to Explain the query will give you all the information about the query behavior as in the table below

mysql> Explain Select id from test where description like’%qui%’;
Result of Explain does not have any indexes !!

Result of the explain query gives the information as follows:

  • ID: It represents the sequence of the query as here we are not using any subquery so there is only ID 1 for the single select query.
  • select_type: In refers to the type of SELECT it can be SIMPLE(as in our case for simple select statements ), UNION (if another select statement in UNION), SUBQUERY (for subqueries) 
  • table: Each row represents the name of the table related to that row. If the subquery is on another table then the second row will have the name of that table. 
  • type: it represents the access type of joins types. It has the values system, ref, eq_ref, full-text, index, range, all. More information on this can be found on MySQL Documentation.
  • possible_keys: All the index keys that can be possibly used in the query.
  • key: This is the most important result of the Explain query. It shows the key which is actually used in the query from the list of possible_keys.
  • key_len: Length of the key that is used in key column in bytes. 
  • ref: It represents the type of values compared to the key in key columns it could be a constant (const) or the results of an arithmetic function (FUNC). In the given example (WHERE password = “value” ) value is a given constant not the result of any function like MAX() or COUNT(). so ref is const not func.
  • rows: Number of rows query processed to get the results. 
  • Extra: This col contains very useful information. It contains values  where (if simple where is used to filter the rows. This may be a hint that we can create an index on that column to make our filters fast), index condition (indexes are used for filtering. Maybe you just created these indexes and now you see here is a good symbol). file sort ( if your query has ORDER BY condition ).

So depends upon the results you get from EXPLAIN query if you find that your possible_keys do not have any indexed keys and Extra info tells you that all the filters are on where the column and not form the indexed key but from simple where condition then you can move a step forward and conclude that you query need indexes.

Hence let us move a step forward to discuss the indexes and how indexes affect the performance of your query. 

Simple Index: If your query has only one column used where condition, in that case, you can create simple indexes. For the query above we can create a simple index like this.

 mysql> create index desc on test (description);

Composite Index: The composite index is a combination of more than one column. If your query has where condition on more that one column then on both the keys you can create a composite index like this

mysql> select id from the users where password = “$jq892hj2898mn1” and type=”user”;

For SQL query like above composite index can be created as follows

 mysql> create index pass_type on users (password, type);

All the indexes you create inside a table can check with the following command

mysql> show indexes from users;

List indexes of table users

Here you can see the indexes of your table

And again if you will check your query with show profiles you will see less execution time for the query with indexes.

So here I end with the final roadmap I followed I Listed my queries with show profile, I examined my query, felt the need of indexes, created indexes (simple or composite) for the columns used in where and order by condition and again checked the performance of my query and it is improved !!!

Leave a Reply

Your email address will not be published. Required fields are marked *

14 + 11 =