469,648 Members | 1,166 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,648 developers. It's quick & easy.

very slow select


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 */;
Jul 23 '05 #1
1 2084
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.
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by DJJ | last post: by
5 posts views Thread by krystoffff | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.