"Jeff" <[EMAIL PROTECTED]> 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
