freefall
07-27-2006, 05:44 PM
Using MySQL, I am looking for the most efficient way to query a database which is in a list format.
By list format I mean that there are many tables (usr_books, usr_movies, etc) with the same two fields (uid and val) plus some additional fields which are unique to certain tables. These tables can be queried by uid to return a list of all favorite books, movies, etc of the user. The remainder of the user information which is meant to only have a single value (like first_name and email) is stored in a single table usr_main.
I am using Perl's DBI module and would like to know if there is any way to make one single query to collect all of the data in all of the tables given the user's uid. I know I can combine a bunch of select statements with unions, but I am looking for something which will allow me to differentiate the different sections in the result set (one set for usr_books, one for usr_movies, etc.) so that I do not have to make 10 or 20 different DBI queries in a row.
Furthermore, would this be more efficient than doing separate queries for everything? The site in question is intended to sustain very heavy traffic.
To phrase this visually, I would like something such as this (anything in brackets is just for this example:
Query the database
while (more tables exist in the resultset) {
while (more results exist from this table) {
my ($uid, $val) = values from this row in the current table
Process data
}
}
I have also thought of maintaining a count of how many items are in each list. Fields such as c_books and c_movies would hold these values in the usr_main table, then I could use a large union statement to get all of the results and then incrementally step through each count and process the values that way.
$sth = $dbh->prepare(qq{
select c_books, c_movies, [...] from usr_main where uid = '$uid'
}); $sth->execute();
my ($c_books, c_movies, [...]) = $sth->fetchrow_array();
$sth = $dbh->prepare(qq{
select uid, val from usr_books where uid = '$uid'
union
select uid, val from usr_movies where uid = '$uid'
}); $sth->execute();
foreach (0..$c_books) {
my ($uid, $val) = $sth->fetchrow_array();
Process data
}
foreach (0..$c_movies) {
my ($uid, $val) = $sth->fetchrow_array();
Process data
}
It seems, however, that there should be a more efficient way to do this.
Thank you for your help.
By list format I mean that there are many tables (usr_books, usr_movies, etc) with the same two fields (uid and val) plus some additional fields which are unique to certain tables. These tables can be queried by uid to return a list of all favorite books, movies, etc of the user. The remainder of the user information which is meant to only have a single value (like first_name and email) is stored in a single table usr_main.
I am using Perl's DBI module and would like to know if there is any way to make one single query to collect all of the data in all of the tables given the user's uid. I know I can combine a bunch of select statements with unions, but I am looking for something which will allow me to differentiate the different sections in the result set (one set for usr_books, one for usr_movies, etc.) so that I do not have to make 10 or 20 different DBI queries in a row.
Furthermore, would this be more efficient than doing separate queries for everything? The site in question is intended to sustain very heavy traffic.
To phrase this visually, I would like something such as this (anything in brackets is just for this example:
Query the database
while (more tables exist in the resultset) {
while (more results exist from this table) {
my ($uid, $val) = values from this row in the current table
Process data
}
}
I have also thought of maintaining a count of how many items are in each list. Fields such as c_books and c_movies would hold these values in the usr_main table, then I could use a large union statement to get all of the results and then incrementally step through each count and process the values that way.
$sth = $dbh->prepare(qq{
select c_books, c_movies, [...] from usr_main where uid = '$uid'
}); $sth->execute();
my ($c_books, c_movies, [...]) = $sth->fetchrow_array();
$sth = $dbh->prepare(qq{
select uid, val from usr_books where uid = '$uid'
union
select uid, val from usr_movies where uid = '$uid'
}); $sth->execute();
foreach (0..$c_books) {
my ($uid, $val) = $sth->fetchrow_array();
Process data
}
foreach (0..$c_movies) {
my ($uid, $val) = $sth->fetchrow_array();
Process data
}
It seems, however, that there should be a more efficient way to do this.
Thank you for your help.