One long query VS many small ones
Posted: Thu Feb 09, 2012 9:44 am
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
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
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....') );
}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