Well, I usually only work with frameworks . I work with frameworks MVC and I usually use the ORMs to query the database.
In my case I use Laravel, but I've used other frameworks , and had the recent opportunity to get to know the Entity Framework.
Some users who are experienced in using the Entity Framework have shown me that the ORM itself generates a query SQL that is different from the one we are accustomed to.
In the same way I've already seen this in the framework Laravel.
What I mean is that in some cases these frameworks usually generate some queries with subqueries , instead of using
For example, I've already seen a query that, to identify if a user has a publication, was being generated like this:
select * from usuarios where (select count(*) from 'posts' where 'usuarios'.'id' = 'posts'.'usuario_id') >= 1
But I believe the same query could be done with JOIN.
Well, now I explain why I'm talking about frameworks before getting into the subject. It is because I have heard several comments from the mouth of "programmers", who say subqueries causes performance problem. But what I can not understand is why the frameworks would then insist on doing something that is harmful (slowing performance is harmful, I meant in that context).
However, if I see it being used in the frameworks , it soon comes to mind that it "has no problem" to use subqueries . And without a problem, you would not understand why the frameworks developers, who would theoretically have enough experience to know what they are doing.I'm an ISSUE in Github, where someone was reporting the performance problem because of the generated subqueries, I noticed that the library developer asked, "But does your tables contain the indexes set correctly?" , which led me to understand that the problem may sometimes be more in the wrong way than using subquery .
So, based on all my arguments above, I ask:
Is it true that setting indexes for a table can improve the performance of a subquery or is it rubbish?