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
