Optimize SQL Query

We generally have dev server and production Server. Usually, Dev server has less data(test data). While working on a Complex application, Simple Query may need to joins of 6 to 8 tables, each table might have 70 to 80 columns.


In such cases, for fewer data, query gives result in less time, When the same query is executed on Production Server, We face performance Issue. To Optimized query, we have to look into depth, how a query is working internally. How many tables getting used? How many columns getting fetch? Does indexing works?


EXPLAIN Keyword is to find all the answer, for all the above question.


While using INNER JOIN, We should follow thumb rule as The Entity (table) which has less volume should be on left side and other should be on the right side.

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.09 sec)

mysql> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
|   331603 |
+----------+
1 row in set (0.10 sec)

mysql> select count(*) from employees e JOIN dept_emp ed where e.emp_no = ed.emp_no;
+----------+
| count(*) |
+----------+
|   331603 |
+----------+
1 row in set (1.11 sec)

mysql> select count(*) from  dept_emp ed JOIN employees e where e.emp_no = ed.emp_no;
+----------+
| count(*) |
+----------+
|   331603 |
+----------+
1 row in set (1.19 sec)

The reason is obvious as INNER JOIN gives a result only if a condition is matched so the record should be in both tables, So we should keep a table with less volume on Left Side.

Let's use EXPLAIN  for above Query:


mysql> explain select count(*) from  dept_emp ed JOIN employees e where e.emp_no = ed.emp_no;
+----+-------------+-------+------------+-------+---------------+---------+---------+--------------------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref                | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+--------------------+--------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL               | 294268 |   100.00 | Using index |
|  1 | SIMPLE      | ed    | NULL       | ref   | PRIMARY       | PRIMARY | 4       | employees.e.emp_no |      1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+--------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)

Simpler Form:

id: 1
select_type: Simple
table: e
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 294268
filtered: 100.00
Extra: Using index

*************

id: 2
select_type: Simple
table: ed
partitions: NULL
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: employees.e.emp_no
rows: 1
filtered: 100.00
Extra: Using index

A Simple select type means it's not a subquery or union.

Type denotes, how rows are accessed, here ref means multiple rows are matched.

Key_len shows, a length of the index.

In the Extra column, we can see, it's using an index. 

So, now we know, How to identify, optimized query! using MYSQL Execution Plan.

Happy Learning!