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/).
Subject
Views
Written By
Posted
1890
August 20, 2016 03:59AM
977
August 20, 2016 09:27AM
901
August 21, 2016 09:40PM
886
August 24, 2016 10:58AM
924
August 24, 2016 05:32PM
882
August 25, 2016 01:31AM
953
August 25, 2016 07:37PM
996
August 27, 2016 04:31AM
Re: Innodb full table scan if query return 0 rows
971
August 27, 2016 01:39PM
1068
August 29, 2016 03:27AM
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.