Hi,
What about SELECT count(*) FROM table1 - SELECT count(*) FROM table1
WHERE status = 1 ? (this query should be mush faster)
Regards,
Jocelyn
jpow wrote:
Hi everyone, I have this problem of slow "count *" when I use a where clause.
1. I have a table of ~1m rows.
2. There is a "status" column which can be 0 or 1.
3. Most of the rows have a status of 0, but maybe 10% of them have a
status of 1.
4. I need to know how many records are status 1 / 0 for
pagination/other purposes.
5. I already have a multi index comprising: id, status
I encountered this issue:
A) SELECT count(*) FROM table1 : Super fast 0.00x secs (select tables
optimized away)
B) SELECT count(*) FROM table 1 WHERE status = 0 : Quite slow ~0.5-0.6
secs (uses where; uses index)
I understand that the the query w/o the where clause is extremely fast
coz the query doesnt even need to access the table index, it can just
retrieve the total no of rows (which is stored)
However, when i put in the WHERE clause, MYSQL needs to access the index.
0.5-0.6 secs is too slow for me, as the db is fast scaling upwards.
Is there any faster way for me to retrieve the total count for rows
with status = 0/1?
Many Thanks
J Pow
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
