very slow select

[David Lawson]

The line indicated below from my php script is very slow (about 10 seconds). I have this field
indexed so I thought that it would be much faster. Could someone tell me what might be wrong?
I'm also including the dump of the table definitions. This is a cd cataloging database.

Right now the filenames table is empty and I'm trying to populate it, but at the rate it's going it
would take days. I have about 700,000 records in the 'files' table, but none in the 'filenames'
table yet.

David

$cmd = "select fname from files";
$result = mysql_query($cmd) or die("error: " . mysql_error() . "\n");

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$fname = $row[0];
echo "$fname...\n";

$cmd2 = "select fname from filenames where fname='$fname'"; <<<<<<<<<<<<< VERY SLOW!
$result2 = mysql_query($cmd) or die ("error: " . mysql_error());

if (mysql_num_rows($result2) != 1) {
echo " adding $fname\n";
$cmd3 = "insert into filenames (fname) values ('$fname')";
mysql_query($cmd3) or die ("error: " . mysql_error());
}
else {
echo " *********** $fname is already in the table\n";
}
mysql_free_result($result2);

}

================================================== =================
-- MySQL dump 10.8
--
-- Host: localhost Database: cddb
-- ------------------------------------------------------
-- Server version 4.1.7-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */;

--
-- Table structure for table `cds`
--

DROP TABLE IF EXISTS `cds`;
CREATE TABLE `cds` (
`cdid` int(10) unsigned NOT NULL auto_increment,
`serno` varchar(16) NOT NULL default '',
`label` varchar(255) default NULL,
PRIMARY KEY (`cdid`),
KEY `serno` (`serno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `filenames`
--

DROP TABLE IF EXISTS `filenames`;
CREATE TABLE `filenames` (
`fid` int(10) unsigned NOT NULL auto_increment,
`fname` varchar(255) default NULL,
PRIMARY KEY (`fid`),
KEY `fname` (`fname`) <<<<<<<<<<<<<<<<<<<<< INDEXED!
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `files`
--

DROP TABLE IF EXISTS `files`;
CREATE TABLE `files` (
`cdid` int(10) unsigned NOT NULL default '0',
`pid` int(10) unsigned NOT NULL default '0',
`fid` int(10) unsigned default NULL,
`fname` varchar(255) NOT NULL default '',
`ext` varchar(20) default NULL,
`size` int(11) NOT NULL default '0',
`fdate` datetime default NULL,
KEY `ext` (`ext`),
KEY `size` (`size`),
KEY `fname` (`fname`),
KEY `fdate` (`fdate`),
KEY `cdid` (`cdid`),
KEY `pid` (`pid`),
KEY `fid` (`fid`),
CONSTRAINT `files_ibfk_1` FOREIGN KEY (`cdid`) REFERENCES `cds` (`cdid`),
CONSTRAINT `files_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `paths` (`pid`),
CONSTRAINT `files_ibfk_3` FOREIGN KEY (`fid`) REFERENCES `filenames` (`fid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `paths`
--

DROP TABLE IF EXISTS `paths`;
CREATE TABLE `paths` (
`pid` int(10) unsigned NOT NULL auto_increment,
`path` varchar(255) NOT NULL default '',
PRIMARY KEY (`pid`),
KEY `path` (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[Bill Karwin]

David Lawson wrote:

$cmd = "select fname from files";
$result = mysql_query($cmd) or die("error: " . mysql_error() . "\n");

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$fname = $row[0];
echo "$fname...\n";

$cmd2 = "select fname from filenames where fname='$fname'"; <<<<<<<<<<<<< VERY SLOW!

General advice for any slow query issue: how slow does this query run
when you execute it in the mysql command shell? Can you run that query
with EXPLAIN to try to figure out whether it's really using the index
you think it should use?

If I were designing this routine, I'd execute the outer query ($cmd) as
the following:

"select f.fname
from files as f left outer join filenames as n
on f.fname = n.fname
where n.fname is null"

Then loop through the result set, which should include _only_ the fnames
that need to be inserted into the filenames table.

Regards,
Bill K.