[jbossseam-issues] [JBoss JIRA] Commented: (JBSEAM-1980) Using primary key in select count()

Using primary key in select count() ------------------------------------ Key: JBSEAM-1980 URL: http://jira.jboss.com/jira/browse/JBSEAM-1980 Project: JBoss Seam Issue Type: Feature Request Components: Tools Affects Versions: 1.2.1.GA Environment: JBoss Seam with MSSQL database connections Reporter: thierry accart Priority: Optional Original Estimate: 1 day Remaining Estimate: 1 day I see in my JBoss log several lines like Hibernate: select count(*) as col_0_0_ from .... I'm not sure how it's handled in mysql, oracle or other databases, but in mssql, indexes are stored in different datapages. When counting rows, if we use select count (primarykey), we scan a much lower number of pages than we currently do with select(*) Example : if one row has a 4 bytes long primary key, and one row stores 1000 bytes, if the table has 20000 rows, sql will store this table in 2500 datapages (one page stores 8096 bytes). a select (*) will scan the 2500 datapages, when a select (primarykey) will scan 1 (one) datapage. As you can see, the impact on database server can be important for large tables with a lot of rows. Rgds