(mysql) fastest method to lookup 2 fields from 2 different tables via perl

sun-flower
sun-flower used Ask the Experts™
on
Hello there

What is the best way in terms of performance to make a lookup in two different tables? There is in each case only 1 row in the result.

I need the address from the 1st table and the image_url from the 2nd table . How can I combine these queries? (the query will run in a while loop)

my $sth = $dbh->prepare("SELECT city, address, c_id FROM table1 WHERE c_id='1125' LIMIT 1");
my $sth = $dbh->prepare("SELECT image_url, zip, c_id FROM table2 WHERE c_id='1125' LIMIT 1");

my $row;
$sth->execute;
$row = $sth->rows;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2004

Commented:
Do table1 and table2 have any inherent relationship?  What is the purpose of the c_id field?

Author

Commented:
yes, c_id is a unique identifier.

example:
table1 has these fields: Authors Books
table2                          :Authors Images
c_id would be Authors
This query will work if there will always be a matching record in both tables:

SELECT table1.c_id, table1.city, table1.address, table2.image_url, table2.zip FROM table1 INNER JOIN table2 ON table1.c_id=table2.c_id WHERE table1.c_id='1125' LIMIT 1;

This query can handle the possibility of having a matching record in one table but not the other:

SELECT (SELECT city FROM table1 WHERE c_id='1125' LIMIT 1), (SELECT address FROM table1 WHERE c_id='1125' LIMIT 1), (SELECT image_url FROM table2 WHERE c_id='1125' LIMIT 1), (SELECT zip FROM table2 WHERE c_id='1125' LIMIT 1);


--Adrian
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Top Expert 2004

Commented:
AdrianSRU's first query is where I was going with this.  Since c_id is a unique identifier present in both tables, use it to create a JOIN and grab all the fields at once.

This will backfire if c_id is not guaranteed to be unique.  In fact, if you have many records with the same c_id in either table, you'll probably get some rather inconsistent results.

Author

Commented:
ok thanks. $row should now have 2 values (address and image_url) How can i print these without a while loop as it is only a single row?

my $sth = $dbh->prepare("SELECT table1.address, table2.image_url FROM table1 INNER JOIN table2 ON table1.c_id=table2.c_id WHERE table1.c_id='1125' LIMIT 1");

my $row;
$sth->execute;
$row = $sth->rows;


Top Expert 2004

Commented:
Not knowing Perl, I'm at a loss.  I can show you an appropriate example in PHP, though:

<?
$query = "SELECT table1.address, table2.image_url FROM table1 INNER JOIN table2 ON table1.c_id=table2.c_id WHERE table1.c_id='1125' LIMIT 1";
$result = mysql_query($query,$link);
$row = mysql_fetch_assoc($result);
// $row['address'] and $row['image_url']
print_r($row);
?>

Author

Commented:
routinet: each unique c_id exists only once per table and all c_ids are existent in both tables. So there shouldn't be a chance of inconsistent results.
Top Expert 2004
Commented:
Then you should be set!  :)
Top Expert 2009
Commented:
The code $row=$sth->rows returns the number of rows, not the data contained in those.  If you want the data, you can either get it from the $sth, or skip that step and get it directly from the $dbh.

Example 1: get all data in 1 statement from dbh
  my $rowref=$dbh->selectall_arrayref($sql);  #where $sql is your SELECT statement
  print "$rowref->[0]->[0], $rowref->[0]->[1]\n";  #prints field 0 (address) and field 1(image_url) in row 0

Example 2: get 1 row from sth using array
    my $sth = $dbh->prepare($sql);  #where $sql is your SELECT statement
    $sth->execute;
    my @row = $sth->fetchrow_array;
    print "$row[0], $row[1]\n";

Example 3: get 1 row from sth using hashref
    my $sth = $dbh->prepare($sql);  #where $sql is your SELECT statement
    $sth->execute;
    my @row = $sth->fetchrow_hashref;;
    print "$row->{address}, $row->{image_url}\n";

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial