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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Slow query log 'Rows_examined' vs 'explain select' rows
3221
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.