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
To Check SQL Profilingmysql> show profiles;
To DIsable SQL Profilingmysql> 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
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
Result of the explain query gives the information as follows:
keycolumn in bytes.
keycolumns 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
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;
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 !!!