Selon Behrang Saeedzadeh <[EMAIL PROTECTED]>:
> Hi all
>
> I was reading the High Performance MySQL book (by O'Reilly) and there
> was mentioned that executing a count(*) is slower on the InnoDB
> engine compared to the MyISAM engine, because InnoDB tables do not keep
> track of the number of the records. I just wanted to know that if
> this performance degradation is a feature of all transactional engines
> (i.e. Oracle, DB2, PostgreSQL, ...) or it's just a weakness of the
> InnoDB engines?
>
> Best Regards,
> Behrang S.
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
Hi Behrang,
ORACLE :
**************
There are two ways to have count(*) in oracle :
statistics not updated :
SQL> select count(*) from titi;
COUNT(*)
----------
655360
statistics updated :
SQL> select num_rows from user_tables where table_name='TITI';
NUM_ROWS
----------
655360
Rows Row Source Operation
------- ---------------------------------------------------
5 TABLE ACCESS FULL TITI
********************************************************************************
--
-- First execution, statistics not updated
--
select count(*)
from
titi
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.07 0.07 0 1000 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.07 0.07 0 1000 0 1
--
-- First execution, with updated statistics
--
select count(*)
from
titi
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.15 0.15 0 2000 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.15 0.15 0 2000 0 2
Yoiu can see that time is the same (it's cumulated here : 2 executions)
--
-- Access from data dictionary
--
select num_rows
from
user_tables where table_name='TITI'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 2 9 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.02 1 21 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.04 3 30 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID OBJ$
1 INDEX RANGE SCAN I_OBJ2 (object id 37)
1 TABLE ACCESS CLUSTER TAB$
1 INDEX UNIQUE SCAN I_OBJ# (object id 3)
1 TABLE ACCESS BY INDEX ROWID OBJ$
1 INDEX UNIQUE SCAN I_OBJ1 (object id 36)
0 INDEX UNIQUE SCAN I_OBJ1 (object id 36)
1 TABLE ACCESS CLUSTER USER$
1 INDEX UNIQUE SCAN I_USER# (object id 11)
1 TABLE ACCESS CLUSTER SEG$
1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (object id 9)
1 TABLE ACCESS CLUSTER TS$
1 INDEX UNIQUE SCAN I_TS# (object id 7)
DB2 :
**********
This is the same. TBSCAN if select count(*), but there are statistics based
count(*).
For example,with REORGCHK (runstats ...), you have count(*) without scanning the
table :
SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2
F3 REORG
----------------------------------------------------------------------------------------
Table : MFATENE.STAFF
MFATENE STAFF 35 0 1 1 - 1575 0 -
100 ---
LOOK At the column CARD.
I will not explain the same thing for sqlserver, sybase ..., but when your RDBMs
have a data dictionnary, you don't need to execute count(*) :o) WITH Updated
statistics of course.
With information_schema in 5.x and higher, innodb will act as it's done in all
the other RDBMS.
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
