MySQL Forums
Forum List  »  Partitioning

Re: Partition Management in MySQL 5.1
Posted by: Phil Hildebrand
Date: May 01, 2008 08:15AM

It shouldn't really be thought of as partitioning vs indexing, but rather partitioning itself (it can be used with indexing).

The main benefit with partitioning is the reduction of I/O (whether that be physical or logical). If you can reduce your I/O for a given query / transaction, it will then reduce your overall query/transaction time.

If you think of a partition as a 'slice' of a given table, then any scan of that slice would be faster than a scan of the full table because there would be fewer I/O operations (assuming that the table does not fit on a single page). If your partitions are setup to coincide with the majority of your query access methods (IE: if most queries are by date_loaded, and you partition on date_loaded) then the optimizer can decide to only scan a given 'slice' or partition of the table.

If you also have an index on a second column (say, load_type), it will be partitioned as well, so if I search based on load_date and load_type, the optimizer would be able to scan just the index 'slice' that coincides with the given table 'slice' or partition.

There are also benefits on inserts and maintenance as well, as I can 'defragment' a given partition without affecting other partitions in the table.

Options: ReplyQuote


Subject
Views
Written By
Posted
14538
October 06, 2005 06:41PM
3119
November 07, 2005 07:36PM
Re: Partition Management in MySQL 5.1
3081
May 01, 2008 08:15AM


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.