Re: question about index

On Thu, 16 Dec 2004 11:41:54 +0100
Jerome Alet <alet(at)librelogiciel(dot)com> wrote:
> For a future databas, I plan to have got a table with a text field
> which can contain only three different values, say "VALUE1",
> "VALUE2", and "VALUE3"

Can it increase? That is, can a "VALUE4" be added half way through the
year?

> this table may have, over the course of one year, several million
> rows for a size around 2 Gb or more.

Doing a COUNT(*) on that many rows could get expensive but...

> I'd be interested in having :
>
> SELECT count(*) AS nbvalue1 FROM mytable WHERE
> myfield='VALUE1'; SELECT count(*) AS nbvalue2 FROM mytable
> WHERE myfield='VALUE2'; SELECT count(*) AS nbvalue3 FROM
> mytable WHERE myfield='VALUE3';

First of all, consider doing it in one statement so that you at least
are not running through it multiple times.

SELECT myfield, COUNT(*) FROM mytable GROUP BY myfield;

Second, consider rules or triggers to keep a separate table up to date
at all times so that you simply have to dump a three row table instead
of going through a huge table counting as you go. We did that in a
similar situation and it made a huge difference. We were doing SUM()
instead of COUNT() and we were calculating an extremely small percentage
of the table - average probably 3 or 4 rows out of 20 million on average
- but it still was worth our while to calculate the sum (balance) on
every transaction rather than calculate it every time. You need to
analyze your own data and usage but this may be a better solution for
you.

As usual, if not completely satisfied you get a full refund.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.