MySQL Forums
Forum List  »  InnoDB

Re: Innodb full table scan if query return 0 rows
Posted by: Rick James
Date: August 27, 2016 01:39PM

It takes time to locate the desired partition. Once there, the index lookup is not enough faster to make up for it.

That is, a non-partitioned table with INDEX(plug, created) will run that query about as fast as a table partitioned on `plug`.

The BTree for 500M rows will be 5 levels deep. The BTree for your partitioned table (5M rows per partition) will be about 4 levels deep. The tradeoff is between picking the partition versus going one extra level in a BTree -- hardly any difference.

[_More discussion_](https://mariadb.com/kb/en/mariadb/partition-maintenance/).

Options: ReplyQuote




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.