MySQL Forums
Forum List  »  InnoDB

Re: Slow query log 'Rows_examined' vs 'explain select' rows
Posted by: Ramon Rockx
Date: January 15, 2013 09:18AM

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`)

> 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!


Options: ReplyQuote

Written By
Re: Slow query log 'Rows_examined' vs 'explain select' rows
January 15, 2013 09:18AM

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.