Re: Slow query log 'Rows_examined' vs 'explain select' rows
Hello Rick,
Thank you for your reply.
> Please provide SHOW CREATE TABLE so we don't have
> to guess.
Yeah, that might come in handy:
CREATE TABLE `visitorsession` (
`vss_id` bigint(20) NOT NULL AUTO_INCREMENT,
`vss_datecreated` datetime DEFAULT NULL,
`vss_externalid` varchar(255) DEFAULT NULL,
`vss_externalidchecksum` bigint(20) DEFAULT NULL,
`tnt_id` int(11) NOT NULL,
`vst_id` bigint(20) NOT NULL,
`vss_referrerlocation` varchar(2048) DEFAULT NULL,
`vss_value` double DEFAULT NULL,
`rsr_id` bigint(20) DEFAULT NULL,
`vss_dateexpired` datetime DEFAULT NULL,
`vss_datelastvisited` datetime DEFAULT NULL,
PRIMARY KEY (`vss_id`),
KEY `fk_vss_vst` (`vst_id`),
KEY `indx_datecreated` (`vss_datecreated`),
KEY `fk_vss_rsr` (`rsr_id`),
KEY `indx_dateexpired` (`vss_dateexpired`),
KEY `indx_datelastvisited` (`vss_datelastvisited`),
CONSTRAINT `fk_vss_rsr` FOREIGN KEY (`rsr_id`) REFERENCES `resource` (`rsr_id`),
CONSTRAINT `fk_vss_vst` FOREIGN KEY (`vst_id`) REFERENCES `visitor` (`vst_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1373557534 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4$$
> Do you have an index _starting_ with
> vss_datelastvisited ?
As you can see indx_datelastvisited has only one column, so that's the first one.
> Sometimes InnoDB gets the "statistics" wrong, and
> does a table scan when it clearly should not.
Well, maybe that's the case, I changed the query by adding 'use index(indx_datelastvisited)' to the 'from' clause. Since then the 'Rows_examined' are as expected and also the query time is much lower.
> Something like this:
> PARTITION BY RANGE (TO_DAYS(vss_datelastvisited))
> would make it run faster
This way, we already partitioned a couple of large tables. However, my last attempt to partition this table resulted in a physically much bigger table. Don't know why, so still have to investigate that one.
> Did you notice that your timespan is 1 second shy
> of a whole number of hours?
This one I don't understand, can you explain further?
Thanks for your help!
Regards,
Ramon