473,883 Members | 1,639 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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($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 */;
Jul 23 '05 #1
1 2247
David Lawson wrote:
$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!


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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4883
by: Tom Yee | last post by:
We just installed SQL Server version 800.194 on a dual processor server equipped with a gigabyte of RAM, running Windows 2000 Server operating system. We set up a few databases with (so far) very tiny tables. When I am working locally (i.e. on the server itself) with Query Analyzer, even the simplest operation is incredibly slow. If I bring up Windows Task Manager looking at the Processes pane (Query Analyzer shows up as "isqlw.exe"),...
11
17592
by: DJJ | last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data from the MySQL tables takes a hell of a long time to load making any kind linkage with my Access data virtually useless. I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting out on a server and the Access database is running locally. The network connection is very...
16
21393
by: mamo74 | last post by:
Hello. I am administering a SQL Server (Enterprise Edition on Windows 2003) from some month and can't understand what is going on in the latest week (when the db grow a lot). The DB is around 250G, and has one table with 1 billion rows. It is performing in a decent way, but can't understand why a particolar table has strong performance problem.
5
10332
by: krystoffff | last post by:
I currently have PostgreSQL 7.1 installed on a server with about 700 Mb of RAM. I have many problems of speed with a database I created. For example, it took almost 12 sec to run the query "select * from table" directly from PostgreSQL, on a table with 4000 records and 60 fields ... And the whole application built on this database is very very slow (some pages take almost 20 seconds to load !) I verifed the indexes, I think they are...
3
4810
by: Janross | last post by:
I'm having trouble with a query that's prohibitively slow. On my free-standing office computer it's fine (well, 2-4 seconds), but on the client's network, it takes at least 5 minutes to run. Obviously not workable! I know where the problem is, I just don't know how to fix it. The query calls a function, and I assume it gets slow because the function runs on every record. So--is there a way to rewrite the function so it's quicker?...
4
24526
by: cefrancke | last post by:
Are there any ways to speed up a Cumulative sum in a query or perhaps another faster way to have a cumulative sum column (call a vba function?). For example, I want to sum up all values under 'Amount', and also have a field call 'Cumulative' and have the sum grow with each record. Using "MyTable", the query results are... ID or Date Amount Cumulative --------------- ------ ----------
0
2631
by: roiavidan | last post by:
Hi, I'm having a bit of a problem with a small application I wrote in C#, which uses an Access database (mdb file) for storing financial data. After looking for a similiar topic and failing to find one, I'm posting the question in hope some one of you guys out there will have the answer for me...! I'll start with what I have, then I'll continue to the problem itself.
9
8690
by: dan | last post by:
within a loop i am building a sql insert statement to run against my (programatically created) mdb. it works but it seems unreasonably SLOW! Sorry, dont have the code here but the jist is very standard (I think!); e.g: # get connection loop
0
1160
by: pooky333 | last post by:
Hello! Please help someone... I am still at work and completely stumped. I am a relative beginner at VB and have put together some apparently awful code (as shown below). It is to compare two incredibly huge lists of strings (which might duplicate if I take out things like - (not sure if relevant!)), find matches and entering text next to each one. It is very slow and completes a loop about 4 times a minute (yes i have timed it). the annoying...
0
9944
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9793
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10752
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10858
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9582
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7134
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5804
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5996
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4619
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.