Hi Jonathon,
MySQL is using the index for both queries,
as the column "key" in the explain result says.
Using index means that MySQL can find all info
it needs by looking at the index only, i.e doesn't
need to look into the table.
See:
http://dev.mysql.com/doc/refman/5.0/en/explain.html
for more.
/Johan
Jonathon Wardman skrev:
Hello,
I've been working on some queries with a large dataset (7.5 million
rows) and have been finding problems with indexes seemingly being
ignored for some queries - this obviously slows the query right down,
I've seen some queries take up to 30 seconds. This only seems to happen
when I use certain other columns in the where clause.
The table I'm working with is this (I've cut out the columns not
relevant to this query to save space):
CREATE TABLE `sales` (
`transaction_id` varchar(255) NOT NULL default '',
`date` date NOT NULL default '0000-00-00',
`road` varchar(255) NOT NULL default '',
`locality` varchar(255) NOT NULL default '',
`district` varchar(255) NOT NULL default '',
`post_town` varchar(255) NOT NULL default '',
`county` varchar(255) NOT NULL default '',
`postcode` varchar(10) NOT NULL default '',
`easting` int(11) NOT NULL default '0',
`northing` int(11) NOT NULL default '0',
PRIMARY KEY (`transaction_id`(40)),
KEY `postcode` (`postcode`),
KEY `road` (`road`),
KEY `locality` (`locality`),
KEY `district` (`district`),
KEY `post_town` (`post_town`),
KEY `county` (`county`),
KEY `northing` (`northing`),
KEY `date` (`date`),
KEY `coords` (`easting`,`northing`)
) ENGINE=MyISAM
The following query does not use the index (as you can see from the
explain output):
SELECT count(*) AS number_of_rows FROM `sales`
WHERE `easting` >= '314981'
AND `easting` <= '315281'
AND `northing` >= '176627'
AND `northing` <= '176927'
AND `date` > '2004-09-01';
+----+-------------+--------+-------+----------------------+--------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------------+--------+---------+------+------+-------------+
| 1 | SIMPLE | sales | range | northing,date,coords | coords
| 8 | NULL | 2781 | Using where |
+----+-------------+--------+-------+----------------------+--------+---------+------+------+-------------+
However, removing the date section of the where clause makes MySQL use
the index it found:
SELECT count(*) AS number_of_rows FROM `landregistry`
WHERE `easting` >= '314981'
AND `easting` <= '315281'
AND `northing` >= '176627'
AND `northing` <= '176927';
+----+-------------+--------+-------+-----------------+--------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+--------+-------+-----------------+--------+---------+------+------+--------------------------+
| 1 | SIMPLE | sales | range | northing,coords | coords | 8
| NULL | 2781 | Using where; Using index |
+----+-------------+--------+-------+-----------------+--------+---------+------+------+--------------------------+
Does anyone know any reason why this might be happening? It seems a
little odd to me. Any ideas would be appreciated.
Thanks,
Jonathon Wardman
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
