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
[EMAIL PROTECTED] 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
