469,313 Members | 2,652 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,313 developers. It's quick & easy.

Counting rows assigned to index value?

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

Oct 5 '05 #1
2 1589
>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
Oct 5 '05 #2

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

Oct 6 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by walexand | last post: by
6 posts views Thread by Brendan.Collins | last post: by
8 posts views Thread by DrNoose | last post: by
7 posts views Thread by sathyashrayan | last post: by
3 posts views Thread by Jim Heavey | last post: by
4 posts views Thread by cbrichards via SQLMonster.com | last post: by
1 post views Thread by Newmanbt | last post: by
2 posts views Thread by jed | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.