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]
