Hi,
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.
But.... who will partition based on is_redirect? If it is for one-off
task, you can just create two separate tables and do 'manual
partitioning' even in sqlite :)
Even though your is_redirect queries may become faster, you just added
*2 cost for every other index operation (as partitions require you to
loop over all indexes for all the lookups not satisfied by
partitioning key).
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.
Right, indexing can be used to achieve the result, without making
other selects slower (kind of). Thats what indexing is for :)
http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html
"When the optimizer can make use of partition pruning in performing a
query, execution of the query can be an order of magnitude faster than
the same query against a nonpartitioned table containing the same
column definitions and data. "
This is obvious, dropping a partition is faster than DELETE that has
to go and maintain the index. I'm not talking about DELETE operations,
but SELECTs, you seem to fail at reading there :-)
a=# insert into category values ('Apple');
ERROR: duplicate key value violates unique constraint "category_pkey"
And how do native language collations work? (and since which version
are they supported per-database? are they supported per-schema? ;-)
I got somewhat incorrect results once I used lt_LT.UTF-8 for my
'initdb' - and default collation was providing incorrect order too, as
well as unique constraints were not enforcing dictionary-order rules.
postgres=# create database xx encoding 'utf8';
CREATE DATABASE
postgres=# \c xx;
You are now connected to database "xx".
xx=# create table t1 (a varchar(255) primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t1_pkey" for table "t1"
CREATE TABLE
xx=# insert into t1 values ('a');
INSERT 0 1
xx=# insert into t1 values ('b');
INSERT 0 1
xx=# insert into t1 values ('ą');
INSERT 0 1
xx=# select * from t1 order by a;
a
---
a
b
ą
(3 rows)
You guys seem to talk about stuff you never used and never really
understood. Good for you, probably much easier that way.
Domas