MySQL Forums
Forum List  »  Optimizer & Parser

Re: Projection of a WHERE attribute
Posted by: Rick James
Date: March 19, 2010 07:37AM

Note that with InnoDB, the PRIMARY KEY is embedded with the data. That is, it has to rummage through the _data_ even when all it needs is the PK index. No, this does not explain what you are seeing.

Note that things are cached. Any timings should be run twice, and report the second time. This will be the 'cached' time and (usually) exclude time to load from disk. Of course once everything is in cache, there is no further need to run twice. No, this does not explain what you are seeing.

How big is the cache? innodb_buffer_pool_size. Looks like you need about 500MB just to hold all the rows for id=1. The whole table probably cannot be read without hitting the disk. This would explain "11 min 59.82 sec". But, alas, it does not explain why it is not "0.34 sec".

I feel that this is a deficiency in InnoDB, and should be reported as a bug, http://bugs.mysql.com/ , as a missing optimization.

Options: ReplyQuote


Subject
Views
Written By
Posted
7209
March 16, 2010 12:03PM
Re: Projection of a WHERE attribute
1830
March 19, 2010 07:37AM


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.