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, mysql@stripped 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@stripped
> > From: fabsk <fabsk+mysql@stripped>
> > 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/mysql?unsub=1
> >
> >
>
