Michael Monnerie wrote:
> On Freitag 03 April 2009 Wallace Tan wrote:
>> select count(1) from t1;
>
> That would have been my next question. I've spoken once to Paul, because
> dbmail uses lots of count(*), but PostgreSQL optimizes this out. Now it
> seems MySQL would have a performance boost using count(1).
>
> Could you please try:
> 1) first, SELECT COUNT(1) FROM dbmail_messageblks;
> and afterwards
> 2) SELECT COUNT(*) FROM dbmail_messageblks;
>
> The order is important: After the first select(), the table will be
> cached, so the 2nd query will be faster. That, BTW, is part of the
> explanation why your 2nd query was much faster than the 1st.
> Still, count(1) should be faster than count(*) I would expect from the
> thread you posted. I do not have a MySQL db with enough data to test
> around. We're using PostgreSQL because things like that happen to exist
> in MySQL since years, and I don't need a DBMS where I have to think for
> it. I wonder why the devs don't manage to work around those problems.
> But no flames please, everybody should use what they prefer.
>
> mfg zmi
In MySQL (using InnoDB engine) there is no difference for between COUNT(*) or
COUNT(1)
because it is 'optimized' to use the PRIMARY index.
The InnoDB PRIMARY key is a clustered index. See previous post.
If I understand this correctly, the PRIMARY key (clustered index) is THE
problem.
So the only viable solution is to force the query to use a non-clustered index.
> SELECT COUNT(1) FROM dbmail_messageblks;
+----------+
| COUNT(1) |
+----------+
| 263339 |
+----------+
1 row in set (2 min 30.44 sec)
> SELECT COUNT(*) FROM dbmail_messageblks;
+----------+
| COUNT(*) |
+----------+
| 263357 |
+----------+
1 row in set (2 min 25.91 sec)
> EXPLAIN EXTENDED SELECT COUNT(1) FROM dbmail_messageblks\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dbmail_messageblks
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 6574840
Extra: Using index
> EXPLAIN EXTENDED SELECT COUNT(*) FROM dbmail_messageblks\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dbmail_messageblks
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 6574840
Extra: Using index
--
Regards,
Wallace
M:94500905
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
