On Tue, Nov 24, 2009 at 7:57 PM, Domas Mituzas midom.lists@gmail.com wrote:
Antony writes:
If it is, you'd probably want to use partitioning
Partitioning makes selects faster only when there's parallel execution on multiple partitions at once.
That's just not at all true, not for PostgreSQL at least. Say you have 100 million records in the page table, of which 20 million are is_redirect=1 and 80 million are is_redirect=0. Say the average size of a record is 100 bytes, so on average 80 records fit in one page. The table is not clustered, or it is clustered on something other than is_redirect. If you run select * from page where is_redirect=1 from a table which is not partitioned, you have to access pretty much all 1.25 million pages. If you partition the table on is_redirect, you only have to access 250,000 pages.
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
I have no idea how this works on MySQL, or if it works on MySQL at all. In MySQL, you could achieve the same thing through clustering, however.