MySQL Forums
Forum List  »  Newbie

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!

Options: ReplyQuote


Subject
Written By
Posted
Slow query; wrong index?
April 19, 2018 03:07PM
April 19, 2018 06:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.