Re: Speed of MyISAM vs. InnoDB

[Chris Nolan]

COUNT(*) is a special case for MyISAM. However, you'll find that 
anything that has a WHERE clause that takes advantage of an index is 
pretty quick for both MyISAM and InnoDB tables.

For instance:

SELECT COUNT(*) FROM table;

Is slow as all buggery on InnoDB, but:

SELECT COUNT(id) FROM table WHERE  id > 0;

Is pretty quick if id is indexed (or a PRIMARY KEY). I use the above 
query to get a count of rows on an InnoDB table when my PRIMARY KEY is 
an AUTO_INCREMENT column.

Regards,

Chris

[EMAIL PROTECTED] wrote:



I think count(*) is a special case: MyISAM holds a record count which it
can access instantly, InnoDB has to count rows. Does the time difference
persist for real queries?
     Alec

Jiří Matějka <[EMAIL PROTECTED]> wrote on 18/02/2004 10:38:13:

 

Hi all,
 I'm using mysql 4.0.17 and I have this problem with speed of innodb
database:
I have simple command like select count(*) from table1, or select field1,
field2 from table1. The table1 and has more than cca 10.000 rows (most of
the fields are integer, only several varchars and several memos) and its
type is InnoDB. Then the query lasts too long, at least several seconds,
sometimes more than 5. If I convert it to MyISAM then the query lasts
usually less then 0.3 second. If the table is small (cca less than 5.000
rows) then there is not big difference...
Is it normal, that InnoDB isn't able to access large table as quickly as
MyISAM? Or is there any parametr to set to make InnoDB run faster? I need
   

to
 

use InnoDB because it supports transactions and MyISAM not...
   



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]
 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]