I already have a primary key on Calendar - let's call it EventID.

The question is which is going to be faster?

SELECT StartTime, EndTime, Description, ReminderNum, ReminderType, ReminderDir FROM Calendar WHERE UserID = ... AND IsReminder = 1

- OR -

SELECT C.StartTime, R.ReminderNum, R.ReminderType, R.ReminderDir FROM Reminders R LEFT JOIN Calendar C ON R.EventID = C.EventID WHERE C.UserID = ...

Also my problem is:

Table Calendar contains let's say 1.000.000 records. It has primary key EventID. EventID goes from 1 to 1.000.000
Table Calendar has a field called UserID. UserID varies between 0 and 5.

If I put an index on UserID, how much will it speed up this query:

SELECT ... FROM Calendar WHERE UserID = 3

I've read that indexes with low selectivity are basically worthless in Firebird. So how can I make that query faster?

Thanks!

----- Original Message -----
From: Peter Faulks
To: firebird-support@yahoogroups.com
Sent: Friday, November 19, 2010 3:26 PM
Subject: Re: [firebird-support] DB design best practices - which solutionisfaster?



I'm still not 100% sure what it is you are doing....

Sounds like you need a separate table then. Join the two on a integer
primary key - use create generator and select gen_id() to create a
sequence. (it is always generally a good thing to make primary keys
meaningless)

When does a reminder expire? would you then delete it from the table?

Why would a reminder be set for after the event?

On Fri, 2010-11-19 at 14:15 +0100, Zd wrote:
> Hello Set,
>
> The problem is that reminders can be set x hours/days/weeks etc.
> before or after an event. Thus I can't say that I only check for
> reminders 1 week before or after the event.
>
> I'm also worried about querying of UserIDs, ie.: SELECT ... FROM
> Calendar WHERE UserID = ...
>
> If I have a table with 200.000 records and only 4 UserIDs, how slow is
> that going to be?
>
> Thanks,
> Zd
>





[Non-text portions of this message have been removed]