|
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 */;
| 
