I think... you don't have an index on the "Incident" field itself, just on (Date, Incident, Type, Task) which means that it concatenates those fields and orders the result - thus this may be virtually useless if you're looking for a specific incident within a large date range. Since your query has a specific incident number, indexing that field would probably help a lot.
Do a SHOW INDEXES FROM DeltaPAF;
To see the indexes that are actually there.
or
EXPLAIN SELECT Date FROM DeltaPAF WHERE Date>="2003-12-11" AND Date<="2004-01-11" AND Incident=98996144;
to see which indexes MySQL is really using.
For example, in the table below, there are really only two indexes, the one primary key index and the second name index. The Seq_in_index column shows the fields that are included in the index but the ones that aren't listed first will be much harder to find. Like a telephone directory, which is ordered by lastname, firstname - both fields are indexed but they are in the same index, so finding a specific firstname still means a full table scan.
Good luck!
mysql> describe test1; +--------+-------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+------------+-------+ | name | varchar(20) | YES | MUL | NULL | | | mydate | date | | PRI | 0000-00-00 | | | number | int(10) | | PRI | 0 | | +--------+-------------+------+-----+------------+-------+ 3 rows in set (0.00 sec)
mysql> show indexes from test1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test1 | 0 | PRIMARY | 1 | mydate | A | NULL | NULL | NULL | | BTREE | |
| test1 | 0 | PRIMARY | 2 | number | A | 0 | NULL | NULL | | BTREE | |
| test1 | 1 | name | 1 | name | A | NULL | NULL | NULL | YES | BTREE | |
| test1 | 1 | name | 2 | mydate | A | NULL | NULL | NULL | | BTREE | |
| test1 | 1 | name | 3 | number | A | NULL | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.15 sec)
Mike Schienle wrote:
Hi all -
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]
