[ILUG] MySQL partitioned tables/performance

[Lance Dryden]

On 02/23/2011 11:54, John McCormac wrote:
> I've been redesigning a historical database with approximately 836
> million rows of data. The main key is alpha-numerical (domain name).
> Using an alpha-numerical table structure and MyISAM, the average row
> count per table is 23 million though on some of the more unpopular
> characters, the count goes down to about 1 million rows per table with
> the largest being approximately 74 million rows. Simple selects are
> quick (less than 0.2 S for most and faster once the table is open). The
> data is historically frozen so there will be no inserts on any of the
> tables. Would there be any performance benefit in partitioning these
> tables?

As long as the primary kind of search is either an = match or an in () 
match, partitioning in markedly improve query speed.  See mysql docs 
regarding "partition pruning."

Cheers,
Lance Dryden