"Jeff" <jsmforum@stripped> wrote on 09/23/2005 11:36:01 AM:
<<rest of thread snipped>>
>
> 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'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?
>
> <<<<<Truncated thread, getting too big>>>>>>
>
>
I would think that life would become easier on the developers because now,
in order to provide transactional security, they will no longer need to
issue LOCK TABLE and UNLOCK TABLE statements but rather START TRANSACTION
and either COMMIT or ROLLBACK statements. Much friendlier from the
developer's standpoint. I guess if you were using a mixed-mode table
(part of one record is held in an InnoDB table while the fields that
needed FT indexes were in a MyISAM table) that would be a bit harder to
work with but the overall performance gains and the stability provided by
the transactional structure should outweigh any developmental overhead.
The data structure should be relatively independent from the application
design. Your data needs to be stored in a manner that is both logically
correct and efficient to access and maintain. The application needs to
work with that design, not the other way around. It's only when the
database design is so complex that practical factors (like memory size or
a really large or complex join) begin to limit the speed of certain SQL
statements that you need to consider compromising between a theoretically
correct design and performance.
For instance, it is possible to normalize a data structure to the point
that it becomes slower to manage than one that is slightly denormalized.
However, start from theory and work backwards. Break an optimal design
only if it creates a significant or required performance increase. Most of
the times, you can gain performance by small shifts in the application
layer (use two smaller queries instead of one larger, more complex one,
use equality matching rather than LIKE,...) or by tuning your index
structures (watch the slow query log and look for patterns of unindexed
column usage; create indexes to fit, consider building covering indexes
for some of your most frequently executed query patterns,...).
IMHO, a good logical data design will promote better code design and will
enhance overall performance by improving the performance of your data
persistence layer. I think your developers need to have their code
reviewed if switching to InnoDB is going to be a big hassle for them. A
complaint like that sends up a red flag for me. I have to wonder what are
they currently doing that isn't going to be supported by the new
format....
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
