Page 1 of 1

One long query VS many small ones

Posted: Thu Feb 09, 2012 9:44 am
by rainerpl
Hi


I have a simple question for php gurus.

Is it better to retrieve sql data all in one query, with lots of duplicate info, or to break the query down into smaller parts.

Here is a small sample of what i mean

I have the following tables
users - images - comments - likes
Where one user can have many images, and comments.
Each comment can have lots of likes

now, i could fetch all the data about one user in one query....e.g select from users, comments, likes, images where user.id matches the foreign key field.
but that would return lots of duplicate info.
e.g getting user“s comments + likes
username - first name - last name - comment 1 - like 1
username - first name - last name - comment 1 - like 2
username - first name - last name - comment 2 - like 1
username - first name - last name - comment 3 - like 1
where user info is duplicate.

Instead, i noticed that in some frameworks...the queries seemed to be broken down and stored in a comfortable array like this

Code: Select all

$user = array();
$user ['personal_info']  = sql_to_array( mysql_query('select * users ....') );
$user ['images']           = sql_to_array( mysql_query('select * from images....') );
$user ['comments']       = sql_to_array( mysql_query('select * from comments....') );
foreach( $user ['comments'] as &$comment){
  $comment['likes'] = sql_to_array( mysql_query('select * from likes....') );
}
etc etc
so in the end there is one array with all the data, instead of one sql resource with all the giant arrays with 'duplicate' data.

Any ideas about this?
Thank you for your time

Re: One long query VS many small ones

Posted: Thu Feb 09, 2012 5:23 pm
by social_experiment
One way to optimize those queries (long or short) is to select only what you need; instead of

Code: Select all

SELECT * FROM table
opt for

Code: Select all

SELECT name, comments FROM table
It would probably also depend on whether the query is written properly, how the database & tables are setup. I would use a single, 'long' query as opposed to several smaller ones.

Re: One long query VS many small ones

Posted: Thu Feb 09, 2012 7:12 pm
by Celauran
social_experiment wrote:One way to optimize those queries (long or short) is to select only what you need; instead of

Code: Select all

SELECT * FROM table
opt for

Code: Select all

SELECT name, comments FROM table
This. 100 times this. SELECT * is always wrong.

Re: One long query VS many small ones

Posted: Thu Feb 09, 2012 8:09 pm
by califdon
The whole purpose of SQL is to return the specific data that you need. In most cases you should be able to do that with one proper query with no redundancy. Never "break the query down". There certainly can be a situation where you will have to use more than one query, but that is rather uncommon, and the example you showed with arrays makes no sense to me.

Re: One long query VS many small ones

Posted: Fri Feb 10, 2012 8:39 am
by rainerpl
Hi

Thanks for the replies.

The reason why i asked if its good/bad to break a query down was because i dont really know what and how sql resource is returned. Is it an object oriented class of some sort, where php constructs the rows after the query is done, or is the resource an actual array of rows.
Im pretty sure that the rows are constructed after the query, by mysql_fetch_assoc(). In that case its definitely better to make long queries and get long arrays back.

What i meant with my example was the following seeming redundancy.
Lets suppose there is one user who has 1000 likes and i need the user info, and the likes. The data returned looks like this
with getting all in one query ( select * from users as u, likes as l where u.user_id=l.fk_user_id AND u.user_id=3)
1000 rows like this.

user_id - first name - last name - address - phone - street - like_id - fk_user_id
user_id - first name - last name - address - phone - street - like_id - fk_user_id
user_id - first name - last name - address - phone - street - like_id - fk_user_id
user_id - first name - last name - address - phone - street - like_id - fk_user_id
...
...

where the info that changes is only the likes part.
now, i assume that the user info part is concatenated to the likes part after the query by php, but this concatenation should still consume cpu.

So, wouldnt it be better to get the data in 2 parts as
select * from user where user_id=3
select * from likes where fk_user_id=3

in that case ill have one row
user_id - first name - last name - address - phone - street

and 1000 rows like this
like_id - fk_user_id


This 'user-likes' situation is just an example. My real situation is much more complicated and consists of 6+ tables etc.
So i guess im basicaly interested in the cpu cost of this "concatenation" of result arrays.

Re: One long query VS many small ones

Posted: Fri Feb 10, 2012 12:59 pm
by califdon
In my opinion, your approach to learning how to use SQL will lead you down an unproductive path. As a long time college database instructor, I recommend that you clear your mind of such thoughts as "breaking down" a query, or even trying at this early stage in your learning, to optimize your queries. There are certainly some opportunities for optimizing for advanced applications, but the criteria for making such analyses are complicated so that I wouldn't recommend that anyone attempt to engage in this thinking without substantial practical experience. Database engines like MySQL and others perform significant optimization internally, creating indexes on the fly that make queries efficient, even temporary tables that facilitate complex queries.

In short, my strong recommendation to you is to concentrate on learning SQL and let the database engine do what it is designed to do. As the other responders have indicated, "SELECT * " is almost always wrong. Specify the columns that you need to use, don't use the naked wildcard. Learn how to Group and use Aggregate functions. Learn how to Join tables. Learn how to construct sub-queries. Don't concern yourself with abstract questions about optimization at this stage of your learning. A couple of years from now, if you have developed maybe a hundred databases, you will be better prepared to understand some advanced operations that might slightly affect the operation of an extremely large and complex database.

I was going to recommend some online tutorials that illustrate some of these points, but after spending some 10 minutes or so with a search engine, I must admit that I didn't find any that I can recommend. Again, I'll emphasize what I think is the important point: learn SQL itself -- how to extract information from a properly normalized relational database using queries. Don't clutter your mind with thoughts of breaking up queries.