Greg Stark <gsstark@MIT.EDU> writes:
> > CREATE UNIQUE INDEX fiveminute_idx ON fiveminute(server,stamp);
> >
> > It probably would have made sense to just have an index on the server column,
> > but I can't remember why (decision was made Nov-2000). I suspect that is the
> > cause of the index bloat.
There's also another option.
Create a single normalized table, but create separate partial indexes for each
server.
Ie:
create index idx_server_1 (fiveminute) on tab where serverid = 1;
create index idx_server_2 (fiveminute) on tab where serverid = 2;
create index idx_server_3 (fiveminute) on tab where serverid = 3;
...
This is a management headache, creating a new index every time you add a new
server. And the performance of the optimizer when there are thousands of
indexes to choose from would be, well, it would be an interesting experiment
:)
But it should be faster than having a single two-column index, largely because
it has half the data to store and read through.
This assumes all queries on the table have a clause like "where serverid = n".
It won't work for "where serverid between n and m" or for things like "order
by serverid, fiveminute"
Also, incidentally, do you have a lot of people downloading more than 4Gb in
five minutes? Maybe just regular integers would be big enough? They would be
faster.
--
greg