CODEWITHSUNDEEP
phpmysql
user1020317
user1020317

Reputation: 654

Mysql: one large select statement? or multiple small statements?

Just wondering what's best practice for the following in terms of efficiency.

I have a table called "blogPosts" with several fields. I also have a table called "comments" which contains a column blogPost_Id.

There could be many comments for one blog post.

I want to retrieve all information about all posts and all sub-comments, is it better to try and contain this in one sql command like this:

SELECT * FROM blogPosts LEFT JOIN comments ON blogPosts.id = comments.blogPost_id

or is it better to do SELECT * from blogPosts

and then do another SELECT * from comments WHERE blogPost_id=postId for every post?

I should add that I will be adding filters to the SQL based on post and comment fields.

Upvotes: 2

Views: 1651

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

For a simple query like the one in the question, let the database do the join. There is no question about this.

There might be some discussion on more complex queries, but databases are designed for processing large amounts of data. Trying to replicate the database work in an application is not represented.

That said, there are some cases where database optimizers/engines do get confused. In such cases, creating temporary tables or doing work in the application may be necessary. This is not a good thing, but reality.

Let the application layer do what the application layer does best. Let the database do what the database does best, and this definitely includes processing the data.

Upvotes: 0

Songo
Songo

Reputation: 5746

As a general rule less round trips to the database is better, so one giant query is normally preferred.

However, that's not a rule. Your query may actually be a lot slower if you don't set up your indexes properly. Another thing to look out for is the complexity and maintainability of your code. I once wrote a very complex query that joined about 17 tables. It was a nightmare to debug and modify.

Bench-marking is crucial as it's your only tool for measuring performance. You may actually find that sometimes splitting a query may improve performance.

As far as I can see, your query is simple enough to do the join in one query and get away with it.

Upvotes: 0

John Conde
John Conde

Reputation: 219924

Connecting to the database is expensive. The less you do it, the better.

Another way to say it, what's faster? Making 1,000 trips to drop off 1,000 boxes or one trip to drop off 1,000 boxes?

Upvotes: 3

Related Questions