Mysql not use index over huge table

Question

I have next table:

CREATE TABLE `test` (
 `fingerprint` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
 `country` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
 `loader` int(10) unsigned NOT NULL,
 `date` date NOT NULL,
 `installer` int(10) unsigned DEFAULT NULL,
 `browser` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `version` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `os` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `language` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 PRIMARY KEY (`fingerprint`, `date`),
 KEY `date_1` (`date`),
 KEY `date_2` (`date`,`loader`,`installer`,`country`,`browser`,`os`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Right now it contains 10M records and will increase per 2M records / day.

My question, why MySQL use "Using Where" on next query:

explain select count(*) from test where date between '2013-08-01' and '2013-08-10'
1   SIMPLE  test    range   date_1,date_2   date_1  3       1601644 Using where; Using index

Update, why next question have type - All and Using where then:

explain select * from test use key(date_1) where date between '2013-08-01' and '2013-08-10'
1 SIMPLE test ALL date_1 null null null 3648813 Using where

Just a comment, you dont need the date_1 index, the date_2 index covers completely the same funcionality — PerroVerd, Aug 07 '13 at 13:25
yes, I know, I add date_1 just to not got answers - use 1 column index — Te Port, Aug 07 '13 at 13:27
possible duplicate of [mysql fix Using where;](http://stackoverflow.com/questions/9533841/mysql-fix-using-where) — juergen d, Aug 07 '13 at 13:28
Second question is indeed a duplicate of "[mysql fix Using where;](http://stackoverflow.com/questions/9533841/mysql-fix-using-where)". @TePort, please see my update nonetheless. — RandomSeed, Aug 07 '13 at 15:10

Javier · Answer 1 · 2013-08-07T13:39:33.237

It does use the index.

It says so right there: Using where; Using index. The "using where" doesn't mean full scan, it means it's using the WHERE condition you provided.

The 1601644 number also hints at that: it means it expect to read roughly 1.6M records, not the whole 10M in the table, and it correlates with your ~2M/day estimate.

In short, it seems to be doing well, it's just a lot of data you will retrieve.

Still, it's reading the table data too, when it seems the index should be enough. Try changing the count(*) with count(date), so date is the only field mentioned in the whole query. If you get only Using index, then it could be faster.

Actually 'Using index' doesn't mean it's using it to find the rows (it also appears on full index scans). type = range means it is using the index to find matching rows. — Vatev, Aug 07 '13 at 13:34

RandomSeed · Answer 2 · 2013-08-07T15:08:30.397

Your query is not just "Using where", it is actually "Using where; Using index". This means the index is used to match your WHERE condition and the index is being used to perform lookups of key values. This is the best case scenario, because in fact the table has never been scanned, the query could be processed with the index only.

Here you can find a full description of the meaning of the output you are looking at.

Your second query only shows the "Using where" notice. This means the index is only used to filter rows. The data must be read from the table (no "Using index" notice), because the index does not contain all the row data (you selected all columns, but the chosen index only covers date). If you had a covering index (that covers all columns), this index would probably be used instead.

Mysql not use index over huge table

2 Answers2