List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:January 23 2006 10:57pm
Subject:Re: count(*) send a wrong value
View as plain text  
fabsk wrote:
 > Hi,
 >
 > I'm facing a strange problem. I am using a database at my Internet
 > provider (Free, France). The type of table is MyISAM (no choice), MySQL
 > 4.1.15. I can do my tests with my PHP code or phpMyAdmin.
 >
 > The definition of my table is:
 > - uid, int
 > - cid, int
 > - response, text
 > - points, int (can be null)
 >
 > keys:
 > - uid, cid
 > - cid, response(4)
 > - cid

If I'm reading this correctly, the third index is redundant.  The multi-column 
index on (cid, response(4)) will function equally well as an index on cid. 
There's no need for a separate single-column index on cid.

 > When I do "select * from my_table where cid=123", I get my 10 records.
 > But when I do "select count(*) from my_table where cid=123" I get "2". I
 > also happens with many other values of "cid" and the bad result is
 > always "2".
 >
 > I can't understand what's happen. It seems to simple, but there should
 > be something. Do you have an idea?
 >
 > Thank you for your attention
 > Fabien

mysql@stripped wrote:
>From the MySQL 4.1 manual
> 
> 12.10.1. GROUP BY (Aggregate) Functions
> 
> COUNT(expr) 
> 
> Returns a count of the number of non-NULL values in the rows 
> retrieved by a SELECT statement. 
> 
> COUNT() returns 0 if there were no matching rows. 
> 
> mysql> SELECT student.student_name,COUNT(*)
>     ->        FROM student,course
>     ->        WHERE student.student_id=course.student_id
>     ->        GROUP BY student_name;
> 
> 
> COUNT(*) is somewhat different in that it returns a count 
> of the number of rows retrieved, whether or not they contain 
> NULL values. 
> 
> 
> COUNT(*) is optimized to return very quickly if the SELECT 
> retrieves from one table, no other columns are retrieved, 
> and there is no WHERE clause. For example: 
> 
> mysql> SELECT COUNT(*) FROM student;
> 
> 
>  This optimization applies only to MyISAM and ISAM tables 
> only, because an exact record count is stored for these 
> table types and can be accessed very quickly. For 
> transactional storage engines (InnoDB, BDB), storing an 
> exact row count is more problematic because multiple 
> transactions may be occurring, each of which may affect the 
> count. 
> 
> COUNT(DISTINCT expr,[expr...]) 
> 
> Returns a count of the number of different non-NULL values. 
> 
> COUNT(DISTINCT) returns 0 if there were no matching rows. 
> 
> mysql> SELECT COUNT(DISTINCT results) FROM student;
> 
> 
> In MySQL, you can get the number of distinct expression 
> combinations that do not contain NULL by giving a list of 
> expressions. In standard SQL, you would have to do a 
> concatenation of all expressions inside COUNT(DISTINCT ...). 
> 
> COUNT(DISTINCT ...) was added in MySQL 3.23.2. 
> 
> Keith

Keith, how does any of this explain Fabien's result?  If

   SELECT * FROM my_table WHERE cid=123;

returns 10 rows, then

   SELECT COUNT(*) FROM my_table WHERE cid=123;

must return 10, or something is wrong.


Fabien, if these are your actual queries and results, then there is certainly a 
problem.  One possibility is that your two queries are optimized differently, 
and one of the two indexes starting with cid is broken.  You should probably run 
a CHECK TABLE, then REPAIR TABLE if needed.  See the manual for details 
<http://dev.mysql.com/doc/refman/5.0/en/table-maintenance-sql.html>.

If that doesn't help, show us your real queries and their results, along with 
the EXPLAIN <http://dev.mysql.com/doc/refman/5.0/en/explain.html> output for 
each.  The output of SHOW CREATE TABLE 
<http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html> would also be 
helpful.

Michael
Thread
count(*) send a wrong valuefabsk23 Jan
  • Re: count(*) send a wrong valuemysql23 Jan
    • Re: count(*) send a wrong valueFabien SK23 Jan
    • Re: count(*) send a wrong valuefabsk23 Jan
    • Re: count(*) send a wrong valueMichael Stassen23 Jan
  • Re: count(*) send a wrong valueRhino23 Jan
    • Re: count(*) send a wrong valuefabsk24 Jan
  • Re: count(*) send a wrong valueHank24 Jan
  • Re: count(*) send a wrong valueMartijn Tonies24 Jan
    • Re: count(*) send a wrong valuefabsk24 Jan
      • Re: count(*) send a wrong valueFabien SK24 Jan
        • Re: count(*) send a wrong valueFabien SK24 Jan