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
2016
August 20, 2016 03:59AM
1017
August 20, 2016 09:27AM
940
August 21, 2016 09:40PM
934
August 24, 2016 10:58AM
963
August 24, 2016 05:32PM
926
August 25, 2016 01:31AM
994
August 25, 2016 07:37PM
1037
August 27, 2016 04:31AM
Re: Innodb full table scan if query return 0 rows
1015
August 27, 2016 01:39PM
1103
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.