For high volume discussion board type work InnoDB is faster. Our
"slow query" log droped 66% just by switching engine types.
Earlier comments about innoDB not supporting "full text" are actually
supposed to read InnoDB doesn't support full text indexes... This is
only important if you really need a full text index.
If you want transactions, ie a series of queries tied together and
all executed in sequence or all rolled back if there's an issue, then
you must choose InnoDB.
There are some areas such as Data Warehousing that benefit from
MyISAM, and there are some that benefit from InnoDB.. for the
majority though there's no clear choice, and I understand that most
people who have tried direct comparisons end up choosing InnoDB based
on performance. If you don't have a clear reason for using one over
the other, compare both with appropriate memory settings and see how
you go.
In a hosting environment you either need to use 4.1 or higher and
innodb_file_per_table to make sure each table get's it's own file...
or you need to make sure you allocate a LOT of disk space to the
shared InnoDB table space to make sure you don't run out.
As to a setting to change the default... the book says:
If you omit the ENGINE or TYPE option, the default storage engine is
used. Normally this is MyISAM, but you can change it by using the --
default-storage-engine or --default-table-type server startup option,
or by setting the storage_engine or table_type system variable.
which in plain language means adda line like this to your my.cnf file:
default-storage-engine=innodb (or myisam or whatever you want it to be)
Users could then still create a table of a different type, but unless
they specify something it will be whatever you set to be the default.
Also be aware that memory settings behave differently for different
storage engines. InnoDB needs it's own memory... MyISAM doesn't use
InnoDB's memory... and so on. If you set your default to be InnoDB
but a user still sets up something in MyISAM then you need to make
sure there is some memory for MyISAM.
Final note.. MySQL needs the mysql database to exists and for the
tables it cares about to be MyISAM... these hold your user
permissions and so on... don't try converting this to InnoDB, that
would be bad.
Best Regards, Bruce
On Sep 15, 2005, at 2:31 AM, Scott Haneda wrote:
I have moved my data from mysql 3 to 4, across various updates,
over the
past few years. I use phpmyadmin generally, and it defaults to
making tables
myisam.
I can not seem to find a really clear answer as to why I want to
use one
over the other. Generally, I use myisam and change it only when I
need to
do a rollback or something like that.
Since I seem to be able to toggle from myisam to innodb without any
adverse
affects, why would one chose one over the other?
Is there a way to prevent the use of myisam if it is old and slowly
being
deprecated? I am a host and do not control the table structure of
clients
databases.
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
