Bug #1844 Slow performance with select count(*)

• Submitted: 15 Nov 2003 15:14
• Modified: 17 Nov 2003 7:28
• Reporter: Ron Gage
• Status: Can't repeat
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.1 alpha
• OS: Linux (Slackware 9.0)
• Assigned to: Dean Ellis
• CPU Architecture: Any

[15 Nov 2003 15:14] Ron Gage

Description:
ok, it's probably because of extra trap code you put in to catch errors and what not, but I tried a simple "SELECT COUNT(*) FROM type_1" against a recently compiled 4.1 alpha, and it took over 4 seconds to return the results.

Mysqld and mysql were running from the same machine so no network should have been involved.  Mysqld is using the my-huge.cnf file as distributed from the source distro (with the replication stuff commented out).

On the same table layout with Mysql 4.0.16, the above SQL takes less than .1 seconds to complete.

FWIW: the table has 1,250,033 records in it.  in it's final form, I expect this table to have over 50 million records in it.

The table is currently defined as an InnoDB table.

Machine is an Athlon XP-2700 with dual 73 gig SCSI drives and 512 meg of ram.  Kernel is 2.4.22.

How to repeat:
Connect to the MySQL instance on 68.61.39.105 for this weekend (15-Nov-03) and try it for yourself.  

Username: gis
password: gis
database: gis

select count(*) from type_1;

[17 Nov 2003 7:28] Dean Ellis

Using the latest sources from the 4.1.1 BitKeeper tree, I do not notice any significant performance differences for such a query with a 4 million row test.  Please try your test using the latest sources if you were not doing so.

Thank you

[27 Jan 2004 10:39] Heikki Tuuri

Hi!

Are you sure those 1 million rows were cached in the InnoDB buffer pool? InnoDB scan the whole table when it calculates SELECT COUNT(*) FROM mytable.

Regards,

Heikki