Counting rows assigned to index value?

[Adam]

In my MYISAM table I have an index (Index_A) on 2 fields (Field_A,
Field_B). There are millions of rows in the table. The cardinality of
Index_A is 53. I think a query to count the number of rows that match
a pair of values for Field_A and Field_B should execute blindingly fast
(in the index count the number of leaf nodes for the B-Tree node
representing the indexed pair of values).

My query looks like:
SELECT count(*) FROM `table_A` WHERE `Field_A`=5 and `Field_B`=1

Why does it take 20 seconds for this query? And how can it be made
faster?

Thanks

[Gordon Burditt]

>In my MYISAM table I have an index (Index_A) on 2 fields (Field_A,

Field_B). There are millions of rows in the table. The cardinality of
Index_A is 53.
Which doesn't indicate that there aren't 99% of the rows with
Field_A=5. If it used the index to find the rows with matching
Field_A, how many would it get?
I think a query to count the number of rows that match
a pair of values for Field_A and Field_B should execute blindingly fast
(in the index count the number of leaf nodes for the B-Tree node
representing the indexed pair of values).

My query looks like:
SELECT count(*) FROM `table_A` WHERE `Field_A`=5 and `Field_B`=1

Why does it take 20 seconds for this query?
How many rows does it return? If it returns several million rows,
it is likely to take that much just scrolling the screen.

Since you don't have an index on (Field_A, Field_B), it will need
to fetch all the records with Field_A=5 to check Field_B.

does
EXPLAIN SELECT count(*) FROM `table_A` WHERE `Field_A`=5 and `Field_B`=1

indicate that it's using the index?
And how can it be made
faster?

Delete all the rows, but I don't think that's what you want.

Gordon L. Burditt

[Adam]

Gordon Burditt wrote:

In my MYISAM table I have an index (Index_A) on 2 fields (Field_A,
Field_B). There are millions of rows in the table. The cardinality of
Index_A is 53.
Which doesn't indicate that there aren't 99% of the rows with
Field_A=5. If it used the index to find the rows with matching
Field_A, how many would it get?

The data is pretty evenly distributed over Field_A and Field_B

I think a query to count the number of rows that match
a pair of values for Field_A and Field_B should execute blindingly fast
(in the index count the number of leaf nodes for the B-Tree node
representing the indexed pair of values).

My query looks like:
SELECT count(*) FROM `table_A` WHERE `Field_A`=5 and `Field_B`=1

Why does it take 20 seconds for this query?

How many rows does it return? If it returns several million rows,
it is likely to take that much just scrolling the screen.

SELECT count(*) ALWAYS returns ONE row
Since you don't have an index on (Field_A, Field_B), it will need
to fetch all the records with Field_A=5 to check Field_B.

does
EXPLAIN SELECT count(*) FROM `table_A` WHERE `Field_A`=5 and `Field_B`=1

indicate that it's using the index?

And how can it be made
faster?

Delete all the rows, but I don't think that's what you want.

Gordon L. Burditt

Actually I guess the index was not (optimized?).
After running OPTOMIZE on the table the query became very fast