Slow query; wrong index?
Posted by:
Dave S
Date: April 19, 2018 03:07PM
I have this query on a large table that seems to run way slower than expected, and trying to figure out what I can do about it.
The index definitions are a mess (and even include a historical mistake), but here's the table def with unimportant columns removed, but the full list of indexes:
CREATE TABLE `sales` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`storeid` int(10) unsigned DEFAULT NULL,
`custkey` char(36) DEFAULT NULL,
`date` int(10) unsigned DEFAULT NULL,
...
PRIMARY KEY (`id`),
KEY `stores` (`storeid`),
KEY `date` (`paytype`) USING BTREE,
KEY `storeid` (`storeid`,`custkey`),
KEY `idx_custname` (`custname`),
KEY `idx_date` (`date`),
KEY `idx_item` (`item`)
Here's the query:
select count(id) from sales where (id >= 26014708) and (id <26801167) and (storeid = 100);
This returns 62 records in 17.8846 seconds. 17 seconds!!
Here's what EXPLAIN gave me:
select_type: SIMPLE
table: sales
type: ref
possible_keys: PRIMARY,stores,storeid
key: storeid
key_len: 5
ref: const
rows: 7208
extra: Using where
If the "key" field means that it used the "storeid" index, then I'm confused as to why that happened, as it doesn't seem right to me.
In contrast, the same query without the storeID field runs in 0.3 seconds, much faster as expected, and here is its EXPLAIN:
select_type: SIMPLE
table: sales
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 706100
extra: Using where, Using index
Is there a way to improve the performance when storeID is included in the WHERE clause?
Thanks!