Optimize JOIN query in MySQL

Question

This is my MySQL in general:

SELECT *
FROM
     Table1
     INNER JOIN Table2 ON (Table1.Table2_id = Table2.id) 
     INNER JOIN Table3 ON (Table1.Table3_id = Table3.id) 
     INNER JOIN Table4 ON (Table1.Table4_id = Table4.id) 
     INNER JOIN Table5 ON (Table1.Table5_id = Table5.id) 
     LEFT JOIN Table6 ON (Table1.Table6_id = Table6.id)  
ORDER BY Table1.barcode DESC 
LIMIT 50

I put index on all the ID (By default there is but for being sure I rechecked them) and also for Table1.barcode.

It's a very slow query on my database (about 15 secs).

I check it without order by and as I excepted that was really fast.

I remove limit 50 and order by, it took the same time (about 15 secs).

I should say that there are for sure lots of data:

Table1: 300442 records,
Table2: 77 records,
Table3: 314085 records,
Table4: 28987 records,
Table5: 127805 records,
Table6: 3230 records

I want to make it fast.

Maybe I can change the * to the field that I need (So I try to do it).

Is changing the join order would help me?

I can increase the memory of server and also the number of CPU and the speed of CPU, in my query which of them is more effective?

Is there any other recommendation?

Thanks in advance.

What are the relationships between the tables? If more than one is not a 1:1 relationship with table1, your query is highly questionable. — Uueerdo
– Uueerdo, Commented Feb 23, 2018 at 19:07
Then your query makes little sense unless you are trying to get every combination of the records of table2, table4, and table5 that are associated with the same record of table1. If there are X, Y, Z records from t4, t5, and t6 (respectively) associated with a record in t1; you will have XYZ records in the result for those records. — Uueerdo
– Uueerdo, Commented Feb 23, 2018 at 19:18
@Uueerdo Actually I retrieve the query of CakePHP to raw query for showing here. There is a search in the web application that is used in all part of project, which is why it's like that. It works good except in on entity that has lots of data — Malus Jan
– Malus Jan, Commented Feb 23, 2018 at 19:21
Lots of queries tend to work fine when there is very little data; a good query should work well (within reason) when there is lots of data. If you actually need such a "sub" cross product, you are often better off doing so client side. — Uueerdo
– Uueerdo, Commented Feb 23, 2018 at 19:25

draganstankovic · Accepted Answer · 2018-03-12 19:09:38Z

1

You should perhaps try to use explain and figure out what is going on:

https://dev.mysql.com/doc/refman/5.7/en/using-explain.html

Is changing the join order would help me?

The join order usually does not matter since most engines would do the order optimization internally. Some also have a way to force join order and check yourself whether for your case you might get better results with a specific order that is different than what the engine generated.

I can increase the memory of server and also the number of CPU and the speed of CPU, in my query which of them is more effective?

I would focus on memory (caching, shared_buffer) but before doing any server change you should first investigate the actual issue well and try to tune your existing system. (for ideas see: https://wiki.postgresql.org/wiki/Performance_Optimization (General Setup and Optimization section)

Maybe I can change the * to the field that I need (So I try to do it).

Definitely. Prefer that to * in general.

edited Mar 12, 2018 at 19:09

answered Feb 23, 2018 at 19:18

draganstankovic

5,4561 gold badge30 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Malus Jan Over a year ago

It's not all of my answer. Would you mind answer them (e.g server specifications) so I'll mark your answer as the answer of my question.

Collectives™ on Stack Overflow

Optimize JOIN query in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related