Efficient MySql Query

Question

I want to design a query where I want to fetch a name from a second table based on the id from the first table. I am able to get the result using the following two queries.

Please, Let me know which one is efficient or gives fast result and why.

SELECT r1.id, c1.categoryName
FROM request r1 
LEFT JOIN category c1
ON r1.categoryId = c1.id
order by r1.dateCreated DESC;


SELECT id, (SELECT categoryName FROM category WHERE id 
= categoryId)
FROM request r1
order by dateCreated DESC;

You can used first query because of JOIN batter than Sub Query and you can visit the URL `https://stackoverflow.com/questions/2577174/join-vs-sub-query` — NikuNj Rathod, Dec 28 '17 at 06:16
Better use first one. Subqueries increases server load as the number of records increases. — , Dec 28 '17 at 06:17
Sub query takes more time rather then the joins. So First one is good — Naveed Ramzan, Dec 28 '17 at 06:20

UHDante · Answer 1 · 2017-12-28T07:22:10.493

Normaly, explicit joins are the faster way to go, because the optimisation is better, but nowdays optimisers are getting better all the time, so you can try to go with a more readable SQL query.

You can find this in the manuals (13.2.10.11 Rewriting Subqueries as Joins): https://dev.mysql.com/doc/refman/5.7/en/rewriting-subqueries.html

It says "A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better".

But in complex cases I prefer subquery, they have more readability, and your SQL will be more maintainable, Sub-queries are the logically correct way to solve problems.

Efficient MySql Query

1 Answers1