Thank you for you answer, but I read many times and I did not found something to answer my question (well, I did not know about the NULL).
In my case: - there is one table - htere is no "distinct" - there is a WHERE clause, so there is no optimisation - there is no other field and no "group by" If I do "count(cid)", I still get "2". Fabien Le lundi 23 janvier 2006 à 20:54 +0000, [EMAIL PROTECTED] a écrit : > >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 > > In theory, theory and practice are the same; > In practice they are not. > > On Mon, 23 Jan 2006, fabsk wrote: > > > To: mysql@lists.mysql.com > > From: fabsk <[EMAIL PROTECTED]> > > Subject: count(*) send a wrong value > > > > 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 > > > > 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 General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
