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]

Reply via email to