I see that the thread is getting too long, so I'm cutting out parts that
I'm not responding to. Hope no one minds.
>>>>As far as
>>>>I'm concerned, simplicity and ease would be to leave it as is but
>>>>we're looking to eek out as much speed in our transactions
>>>
>>>as possible
>>>
>>>>so simplicity, ease and performance don't always go together.
MySQL allocates memory to MyISAM and InnoDB separately, so if you want
to 'eek out' as much performance as possible from the InnoDB side of
things, you will probably want to reduce the amount of memory allocated
to MyISAM (on that server). For this reason, I would recommend changing
all tables (except those in the 'mysql' database, as Shawn explained) to
InnoDB, and essentially dedicating that server to InnoDB. You do have to
leave some memory for MyISAM (8M is enough if your only tables in MyISAM
are the ones in the 'mysql' database).
The configuration settings which determine memory allocation are still
somewhat confusing to me (I'm sure others on the list have a clearer
understanding than I), but the main ones are key_buffer_size for MyISAM
and innodb_buffer_pool_size for InnoDB.
links to the docs:
http://dev.mysql.com/doc/mysql/en/server-system-variables.html
http://dev.mysql.com/doc/mysql/en/innodb-start.html
Is that the only diff (other than the "select count(*)" thing) between
InnoDB and MyISAM? Aren't select statements faster from MyISAM tables
than from InnoDB's?
There are cases when each one performs better than the other. I'll go
over a couple examples from my own experiences... If your table is being
written to very frequently, then InnoDB will yield faster reads because
of table locking restrictions on MyISAM tables. And, if you have
commonly repeated queries, turning on (or turning up) the query cache
will have a more noticeable difference on speed than anything else.
However, for very large tables that are primarily read from (without
repeating the same questions such that they could be cached), I do
believe MyISAM will give better performance.
There's also been a statement from our lead developer that having a db
with mixed tables (some InnoDB and some MyISAM) will make life harder on
them because it makes development of application more difficult. I do
quite a bit of php and some perl programming that interacts with MySQL
and I can't think of any major problems created by a mixed engine type
environment. Sure "select count(*) from table" won't be as fast in an
InnoDB table and obviously full text indexes won't be there but other
than that, I don't see this as causing any "difficulties" for a
programmer. I'm also under the belief that it's not the world's job to
make the programmer's life easier, it's the programmers job to make the
world's life easier.
Does anyone have any input on that?
If your developers need to use transactions to modify multiple tables at
once, then all those tables must be in InnoDB for the transaction to
work. However, besides the few points already mentioned, it really is
transparent to the programmers what storage engine you use. I'd ask them
how they think it will make life "harder on them"; chances are they are
basing this on misconceptions.
While I was writing this, I see that Shawn has responded to this
particular question with a lot more clarity than I would have, so I'll
just agree with what he wrote and stop here :)
Regards,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
