I have a speed problem that I don't understand. I've been pretty active with DB's for a few years, but I'm no expert, so let me know if I'm missing the obvious. I have Paul DuBois' MySQL book (New Riders edition) and Descartes and Bunce's Programming DBI book on my desk, so feel free to reference something there if that will help.
Here's the table I'm working from and it's structure:
CREATE TABLE DeltaPAF (
Date DATE NOT NULL,
Type VARCHAR(4) NOT NULL,
Incident INT UNSIGNED NOT NULL,
Mgr VARCHAR(4) NOT NULL,
Site VARCHAR(40) NOT NULL,
Task ENUM('Proposed', 'Approved', 'Completed', 'Invoiced',
'Expired', 'Rejected', 'Cancelled') NOT NULL,
Webpage MEDIUMTEXT NOT NULL,
Budget DECIMAL(12, 2) DEFAULT 0.00,
PRIMARY KEY (Date, Incident, Type, Task),
INDEX (Type, Mgr, Site)
);I have about 125,000 records in the table and it's running on an older 400 MHz MacOS X 10.2.8 system. The MySQL version is 3.23.52.
The following query comes back with 210 records in about 0.6 seconds.
mysql> SELECT Date FROM DeltaPAF WHERE Date>="2003-12-11"
-> AND Date<="2004-01-11" AND Incident=98996144;However, this query comes back with 210 records in a little over 2 minutes.
mysql> SELECT Budget FROM DeltaPAF WHERE Date>="2003-12-11"
-> AND Date<="2004-01-11" AND Incident=98996144;
Can someone clue me in how I might get the SELECT Budget query to return in a similar time to the SELECT Date query? I tried adding an index for Budget, knowing it shouldn't help, and it didn't. FWIW, the Webpage fields average about 5K characters, but can be as much as 40K.
Mike Schienle, Custom Visuals http://www.customvisuals.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
