CODEWITHSUNDEEP
mysqljoin
Malus Jan
Malus Jan

Reputation: 2148

Optimize JOIN query in MySQL

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.

Upvotes: 0

Views: 61

Answers (1)

draganstankovic
draganstankovic

Reputation: 5426

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.

Upvotes: 1

Related Questions