Trouble using LEFTJOIN when table 2 contains 60,000+ records!

[Antivirus]

I am having BIG problems with pageload times when executing the following query:

$queryvenues = $db->query_read("
SELECT " . TABLE_PREFIX . "erc_venue.*, " . TABLE_PREFIX . "dma.*
FROM " . TABLE_PREFIX . "erc_venue
LEFT JOIN " . TABLE_PREFIX . "dma ON " . TABLE_PREFIX . "dma.zipcode = " . TABLE_PREFIX . "erc_venue.venuezipcode
ORDER BY " . TABLE_PREFIX . "erc_venue.venuetitle
");


Just to give you all an idea of my db, the erc_venue table contains more than 1,000 records and the dma table contains more than 60,000 records...

FYI, the function i have created to display the results is as follows:

function print_venue_row($venue)
{
if ($venue['venuecountry'] = 'USA' AND $venue['venuezipcode'] != 0)
{
$venuedma = $venue['dmaname_clean'] . " (" . $venue['rank'] . ")";
}
else
{
$venuedma = '';
}

print_cells_row(array(
"<a href=\"erc_editvenue.php?venueid=".$venue['venueid']."\">".$venue['venuetitle']."</a>",
$venue['venuecitystate'],
$venuedma,
$venue['venueid'],
"<a href=\"".$venue['venueurl']."\" target=\"_blank\"><img src=\"../images/buttons/ip.gif\" alt=\"".$venue['venueurl']."\" border=\"0\" /></a>",
"<input type=\"radio\" name=\"idforaction\" value=\"".$venue['venueid']."\">"
));
}


When executing this query, it can take as long as 122 seconds to load the page! Is there any way I could re-write the query (or the function print_venue_row) more efficiently to help load the page more quickly?

Thanks in advance for your assistance :)

[Paul M]

What indexes do you have on the tables ?

Can you run in debug mode, and then the explain view, and post the result.

[Antivirus]

Thanks, for dma table, my source is as follows:

`dmacode` int(10) unsigned NOT NULL auto_increment,
`zipcode` int(5) unsigned zerofill NOT NULL default '00000',
`rank` tinyint(2) unsigned zerofill NOT NULL default '00',
`dmaname_clean` varchar(250) NOT NULL default '',
PRIMARY KEY (`dmacode`)


for erc_venue table my source is:

`venueid` int(6) unsigned NOT NULL auto_increment,
`venuetitle` varchar(30) NOT NULL default '',
`venueaddy` varchar(50) default NULL,
`venuecitystate` varchar(50) default NULL,
`venuezipcode` int(5) unsigned zerofill NOT NULL default '00000',
`venuecountry` varchar(20) default 'USA',
`venueurl` varchar(80) default NULL,
`venuephone` varchar(12) default NULL,
PRIMARY KEY (`venueid`)


and the explain thingie is as follows:

SELECT erc_venue.*, dma.*
FROM erc_venue
LEFT JOIN dma ON dma.zipcode = erc_venue.venuezipcode

ORDER BY erc_venue.venuetitle
LIMIT 0, 12
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE erc_venue ALL 98 Using temporary; Using filesort
1 SIMPLE dma ALL 45534

Time Before: 0.27991 seconds
Time After: 7.76009 seconds
Time Taken: 7.48018 seconds


Thanks!

[Antivirus]

bump

[Adrian Schneider]

Add an index on your zipcode columns (both tables) - this will help a lot.

Also, if you alias your tables - you will no longer have to specify the table prefix all over the place: $queryvenues = $db->query_read("
SELECT erc_venue.*, dma.*
FROM " . TABLE_PREFIX . "erc_venue as erc_venue
LEFT JOIN " . TABLE_PREFIX . "dma as dma on (dma.zipcode = erc_venue.venuezipcode)
ORDER BY erc_venue.venuetitle
");

[Antivirus]

Thanks SirAdrian,
that was exactly the problem. I had indexed `dmacode` but needed to also create an index on `zipcode`.

The query SCREAMS now. thanks again :)


(going to work on using aliases from now on as well)