On Tue, Nov 24, 2009 at 7:57 PM, Domas Mituzas <midom.lists(a)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.