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($cm d) or die("error: " . mysql_error() . "\n");
while ($row = mysql_fetch_arr ay($result, MYSQL_NUM)) {
$fname = $row[0];
echo "$fname...\ n";
$cmd2 = "select fname from filenames where fname='$fname'" ; <<<<<<<<<<<<< VERY SLOW!
$result2 = mysql_query($cm d) or die ("error: " . mysql_error());
if (mysql_num_rows ($result2) != 1) {
echo " adding $fname\n";
$cmd3 = "insert into filenames (fname) values ('$fname')";
mysql_query($cm d3) or die ("error: " . mysql_error());
}
else {
echo " *********** $fname is already in the table\n";
}
mysql_free_resu lt($result2);
}
=============== =============== =============== =============== =======
-- MySQL dump 10.8
--
-- Host: localhost Database: cddb
-- ------------------------------------------------------
-- Server version 4.1.7-nt
/*!40101 SET @OLD_CHARACTER_ SET_CLIENT=@@CH ARACTER_SET_CLI ENT */;
/*!40101 SET @OLD_CHARACTER_ SET_RESULTS=@@C HARACTER_SET_RE SULTS */;
/*!40101 SET @OLD_COLLATION_ CONNECTION=@@CO LLATION_CONNECT ION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHE CKS=@@UNIQUE_CH ECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KE Y_CHECKS=@@FORE IGN_KEY_CHECKS, FOREIGN_KEY_CHE CKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@ @SQL_MODE, SQL_MODE="NO_AU TO_VALUE_ON_ZER O" */;
--
-- 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_S QL_MODE */;
/*!40014 SET FOREIGN_KEY_CHE CKS=@OLD_FOREIG N_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@ OLD_UNIQUE_CHEC KS */;
/*!40101 SET CHARACTER_SET_C LIENT=@OLD_CHAR ACTER_SET_CLIEN T */;
/*!40101 SET CHARACTER_SET_R ESULTS=@OLD_CHA RACTER_SET_RESU LTS */;
/*!40101 SET COLLATION_CONNE CTION=@OLD_COLL ATION_CONNECTIO N */;
Comment