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