Re: Partition Management in MySQL 5.1

[Phil Hildebrand]

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.