Steven Roussey wrote:
>
> Arg!
>
> OK, I switched to the second method below on the site itself. Now things are
> somewhat reversed. Count(*) seems somewhat faster, and both methods are
> creating pauses. Then it occurred to me that both methods are relying on the
> index only. So I'm now using a third method (Type 3, Type 1 and 2 are
> repeated further, below):
>
> select b from tbl where a=# and c=0;
> count number of rows in result set.
>
> I'm selecting a column that is not part of the index used in the where
> clause.
>
> The results are interesting. With no background traffic, Type 1 is twice as
> fast as Type 2 which itself is twice as fast of Type 3. So on a benchmark
> level, I'm selecting a query that is four times slower than the optimal
> choice.
>
> But when there is real traffic hitting the server at the same time, things
> change. But not as much on the average, so much as on the maximum query
> length:
>
> Type 1: average is 0.15165980696678 with max of 9.3659509420395
> Type 2: average is 0.2846958220005 with max of 5.8125519752502
> Type 3: average is 0.2899955201149 with max of 0.071884989738464
>
I can't help you with your main question, but there seems to a problem
with this data.
Either your decimal places are off or this data is unreliable. The max
query time can't be less than the average time.
jim...
