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
1732
August 20, 2016 03:59AM
907
August 20, 2016 09:27AM
833
August 21, 2016 09:40PM
822
August 24, 2016 10:58AM
830
August 24, 2016 05:32PM
798
August 25, 2016 01:31AM
890
August 25, 2016 07:37PM
935
August 27, 2016 04:31AM
Re: Innodb full table scan if query return 0 rows
886
August 27, 2016 01:39PM
1004
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.