Optimize Queries for Speed and Performance

Tip #1: Index All Columns Used in 'where', 'order by', and 'group by' Clauses

Though indexes may take up more space and decrease performance on inserts, deletes, and updates. However, indexes can considerably reduce select query execution time.

without index => full table scan

mysql> select customer_name from customers where customer_id='140385';

The above query will force Database server to conduct a full table scan (start to finish) to retrieve the record that we are searching.

Database server has a special 'EXPLAIN' statement that you can use alongside select, delete, insert, replace and update statements to analyze your queries.

Once you append the query before an SQL statement,

mysql>explain select customer_id, customer_name from customers where customer_id='140385';

Server displays information from the optimizer about the intended execution plan.

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          |NULL| NULL    | NULL | 500|    10.00 |Using where |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

As you can see, the optimizer has displayed very important information that can help us to fine-tune our database table.

First, it is clear that MySQL will conduct a full table scan because key column is 'NULL'.

Second, MySQL server has clearly indicated that it's going to conduct a full scan on the 500 rows in our database.

To optimize the above query, we can just add an index to the 'customer_id' field.

If we run the explain statement one more time, we will get the below results:

mysql> Explain select customer_id, customer_name from customers where customer_id='140385';

+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |

+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

|  1 | SIMPLE      | customers | NULL       | ref  | customer_id   |customer_id| 13      | const |   1 |   100.00 | NULL  |

+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

From the above explain output, it's clear that MySQL server will use our index (customer_Id) to search the table.

You can clearly see that the number of rows to scan will be 1. Although I run the above query in a table with 500 records, indexes can be very useful when you are querying a large dataset (e.g. a table with 1 million rows).

Tip #2: Optimize Like Statements With Union Clause

Sometimes, you may want to run queries using the comparison operator 'or' on different fields or columns in a particular table. When the 'or' keyword is used too much in where clause, it might make the MySQL optimizer to incorrectly choose a full table scan to retrieve a record.

A union clause can make the query run faster especially if you have an index that can optimize one side of the query and a different index to optimize the other side.

Example, consider a case where you are running the below query with the 'first_name' and 'last_name' indexed:

mysql> select * from students where first_name like  'Ade%' OR last_name like 'Ade%' ;

The query above can run far much slower compared to the below query which uses a union operator merge the results of 2 separate fast queries that takes advantage of the indexes.

mysql> select  from students where first_name like  'Ade%' UNION ALL select  from students where last_name like 'Ade%' ;

Tip #3: Avoid Like Expressions With Leading Wildcards

MySQL is not able to utilize indexes when there is a leading wildcard in a query. If we take our example above on the students table, a search like this will cause MySQL to perform full table scan even if you have indexed the 'first_name' field on the students table.

mysql> select * from students where first_name like  '%Ade'  ;

We can prove this using the explain keyword:

mysql> explain select * from students where first_name like  '%Ade' ;

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    11.11 | Using where |

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

As you can see above, MySQL is going to scan all the 500 rows in our students table and make will make the query extremely slow.

Tip #4: Take Advantage of MySQL Full-Text Searches

If you are faced with a situation where you need to search data using wildcards and you don't want your database to underperform, you should consider using MySQL full-text search (FTS) because it is far much faster than queries using wildcard characters.

Furthermore, FTS can also bring better and relevant results when you are searching a huge database.

To add a full-text search index to the students sample table, we can use the below MySQL command:

mysql>Alter table students ADD FULLTEXT (first_name, last_name);

mysql>Select * from students where match(first_name, last_name) AGAINST ('Ade');

In the above example, we have specified the columns that we want to be matched (first_name and last_name) against our search keyword ('Ade').

If we query the optimizer about the execution plan of the above query, we will get the following results:

mysql> explain Select * from students where match(first_name, last_name) AGAINST ('Ade');

+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

| id | select_type | table    | partitions | type     | possible_keys | key        | key_len | ref   | rows | filtered | Extra                         |

+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

|  1 | SIMPLE      | students | NULL       | fulltext | first_name    | first_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |

+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

It's clear that only a single row will be scanned even if our student's database has 500 rows and this will speed up the database.

LIKE vs CONTAINS on SQL Server

Which one of the following queries is faster (LIKE vs CONTAINS)?

SELECT * FROM table WHERE Column LIKE '%test%';

or

SELECT * FROM table WHERE Contains(Column, "test");

The second should be faster, because it can use some form of index (in this case, a full text index).

Of course, this form of query is only available if the column is in a full text index. If it isn't, then only the first form is available.

The first query, using LIKE, will be unable to use an index, since it starts with a wildcard, so will always require a full table scan.