--- In firebird-support@yahoogroups.com, "hay77772000" <dhay@...> wrote:

>
> Hi,
>
> We have a table that grows very rapidly, and can contain millions of
> records. We're trying to figure out the best indexing on it. Usually
> we will display the records in the UI based on the record's timestamp,
> in ascending or descending order.
>
> Is it a problem to create 2 indexes on this field (asc and desc)?

Two ascending indices or two descending indices are bad, but one of
each is fine.

Do not add an ascending index to a field that is on its own subject to
a foreign key, primary key or unique constraint as these constraints
will already add an index to that field.

> I
> seem to remember reading in the Firebird book that you shouldn't index
> timestamp fields?

If you can back this up with a page number I am sure a lot of people
would be interested. I can't see any reason not to and I do index
timestamp fields myself for various reasons.

>
> It also has an id which is a foreign key to another table. We will
> therefore have 3 indexes on the table. Will that be a problem
> performance-wise when we write to it (many times a second)?

Many times per second doesn't sound like much, but it doesn't sound
like you are being very precise about it. Asking whether something is
a problem is subjective.

Insert performance is relative to the size of the record and the
number, the number of indices that must be updated and any triggers
that are subsequently fired, and of course the power of the database
server.

I often see insert speeds of close to 10,000 per second with a primary
key and a couple of foreign key fields. You will need to do some
homework yourself though. Test your throughput with YOUR hardware and
YOUR structure with indices disabled and repeat the test with indices
enabled. See whether the difference is enough to care about.

Adam