How To Optimize MySql QUERY For JOIN Query Operation

Continuing the previous post about Join Operation on MySQL Query. I want to share tips or how to Optimize a Query. when Data in Database is large, a common problem is becoming very slow Query execution. there are some things that cause it to happen, one of which is, not efficient in application of Query, here I just wanted to share a trick to speed up the execution of Query that I know.

In a case:

Table_1

ID	NAME	ID_GROUP
1	RAMA	1
2	SARI	2
3	DIAN	2

Table_2

ID	SCORE
1	300
2	200
3	350

Table_3

ID_GROUP	GROUP_NAME
1	GROUP A
2	GROUP B
3	GROUP C

"We have three tables and we want to combine the three fields in the tables with specific criteria, for example, id_group field that has a value = 2 in Table_3".

Generally, the format of the query will be like this:

"SELECT * FROM Table_1 INNER JOIN Table_2 ON Table_1.ID = Table_2.ID INNER JOIN Table_3 ON Table_1.ID_GROUP = Table_3.ID_GROUP WHERE Table_1.ID_GROUP = '2';"

That query is correct. but, if the data is large will take a long time during the execution. because, it must combine between Table_1 and Table_2 first, and then the result of that combine will combine with Table_3, then, will search criteria in the third result of the combination.

Imagine if the data is large!

The logic is. First, You must sets criteria on the front to get data record in Table_1 that has id_group value = 2, so that get small initial data. Same result but not same execution time.

look at the following query :

"SELECT * FROM Table_1 INNER JOIN Table_2 ON Table_1.ID_GROUP = '2' AND Table_1.ID = Table_2.ID INNER JOIN Table_3
ON Table_1.ID_GROUP = Table_3.ID_GROUP"

RESULT