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

[thierry accart (JIRA)]

    [ http://jira.jboss.com/jira/browse/JBSEAM-1980?page=comments#action_12378956 ] 
            
thierry accart commented on JBSEAM-1980:
----------------------------------------

You're perfectly right : execution plan for 
  selection count(*) from xxx
and 
  select count (PKey) from xxx
are returning the same plan : only the primary key is scanned.

Maybe some people count check if oracle, mysql and others are making the same optimization ; if not, my suggestion still makes sense (and if yes, I'll be ashamed !)



> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira