Wednesday, 7 August 2013

Mysql not use index over huge table

Mysql not use index over huge table

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

No comments:

Post a Comment