Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Counting rows assigned to index value?
October 5, 2005, 7:32 am
rate this thread
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
Re: Counting rows assigned to index value?
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?
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.
EXPLAIN SELECT count(*) FROM `table_A` WHERE `Field_A`=5 and `Field_B`=1
indicate that it's using the index?
Delete all the rows, but I don't think that's what you want.
Gordon L. Burditt
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum
- » ssh on command line: force using a group size (prime size) of 1024 (and no...
- — The site's Newest Thread. Posted in » Secure Shell Forum