| Subject | Re: [IBO] Database theory, help needed. |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2001-08-13T13:51:54Z |
Hi Dave!
As Lester pointed out, it is the indexes that matters. Ideally, you want
your query to use one index, no more, no less. But, I disagree with Lester
in which fields you should index. You want the index to be as restrictive
as possible, and greater or less than normally isn't very restrictive. My
guess is that an index on (column1, column4) or (column4, column1) is what
you want, but if e.g. column2 is an index on a date field and you typically
are interested in records inserted today - then that field could of course
be included in the index as well.
There's one trap that is easy to fall into, and that is to execute queries
that use too many indexes. This can slow down execution quite a bit.
to prepare your query and then just run it whenever the user changes
his/her mind. This query should be quick anyway, hopefully less than a
second (which most users are content to wait).
ordering a chocolate anchovies pizza? I'd actually like one of those - I
have this dreaded disease that requires a healthy lifestyle with fish 3-4
days a week, and that variation sounded like something I just might try.
HTH,
Set
>I have an SQlSounds like a nice, tiny table...
>Select Count(column1)
>From Mytable
>where column2 > :A
>and column3 < :B
>and column4 = :D
>and column1 = :E
>there are say 20,000 records in Mytable.
>and the result is likely to be about 25.
>Given the result, how much is the speed of this sql going to depend on
>1. The number of fields in Mytable ?
>2. The number of records in MyTable ?
As Lester pointed out, it is the indexes that matters. Ideally, you want
your query to use one index, no more, no less. But, I disagree with Lester
in which fields you should index. You want the index to be as restrictive
as possible, and greater or less than normally isn't very restrictive. My
guess is that an index on (column1, column4) or (column4, column1) is what
you want, but if e.g. column2 is an index on a date field and you typically
are interested in records inserted today - then that field could of course
be included in the index as well.
There's one trap that is easy to fall into, and that is to execute queries
that use too many indexes. This can slow down execution quite a bit.
>Given that the parameters A and B are known some 5 minutes in advanceYes, this sounds like a very stupid idea ;o) I'd rather say it is important
>of parameters D and E.
>And Parameters D and E are changed by the user quite often (say 6
>times in 2 minutes as his mind changes) and the query re-run, I
>originally programmed to select on parameters A and B into another
>table. Then selected from that much smaller table using D and E to
>get the result...but I am beginning to doubt whether that was really
>desirable given the overhead of the transfer to a smaller table.
to prepare your query and then just run it whenever the user changes
his/her mind. This query should be quick anyway, hopefully less than a
second (which most users are content to wait).
>Full marks will be obtained for the corect answer to one or moreHurray, but what can I do with those marks? Do they give me a discount when
>questions!
ordering a chocolate anchovies pizza? I'd actually like one of those - I
have this dreaded disease that requires a healthy lifestyle with fish 3-4
days a week, and that variation sounded like something I just might try.
HTH,
Set