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
1833
August 20, 2016 03:59AM
940
August 20, 2016 09:27AM
873
August 21, 2016 09:40PM
860
August 24, 2016 10:58AM
875
August 24, 2016 05:32PM
853
August 25, 2016 01:31AM
925
August 25, 2016 07:37PM
969
August 27, 2016 04:31AM
Re: Innodb full table scan if query return 0 rows
936
August 27, 2016 01:39PM
1038
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.