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
Maintained by the ILUG website team. The aim of Linux.ie is to
support and help commercial and private users of Linux in Ireland. You can
display ILUG news in your own webpages, read backend
information to find out how. Networking services kindly provided by HEAnet, server kindly donated by
Dell. Linux is a trademark of Linus Torvalds,
used with permission. No penguins were harmed in the production or maintenance
of this highly praised website. Looking for the
Indian Linux Users' Group? Try here. If you've read all this and aren't a lawyer: you should be!
