0
0
Index Columns Used in ‘where’, ‘order by’ and ‘group by’ Clauses
Apart from guaranteeing uniquely identifiable records, an index allows MySQL server to fetch results faster from a database. An index is also very useful when it comes to sorting records.
MySQL indexes may take up more space and decrease performance on inserts, deletes and updates. However, if your table has more than 10 rows, they can considerably reduce select query execution time.
It is always advisable to test MySQL queries with a ‘worst case scenario’ sample amount of data to get a clearer picture of how the query will behave on production.
Consider a case where you are running the following SQL query from a database with 500 rows without an index:
mysql> select customer_id, customer_name from customers where customer_id='140385';The above query will force MySQL server to conduct a full table scan (start to finish) to retrieve the record that we are searching.
Luckily, MySQL 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 displays information from the optimizer about the intended execution plan.
If we run the above SQL one more time with the explain the statement, we will get a full picture of what MySQL will do to execute the query:
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 | 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 using the below syntax:
mysql> Create index customer_id ON customers (customer_Id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0If 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).
References
About Author
