#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    155
    Rep Power
    17

    Mix and match InnoDB and MyISAM in same DB?


    I have a database, and I want to use transactions which means innoDB, rather than MyISAM tables, at least for those tables that I would use in the transactions. But I can have both types in the same DB, I think? Is there any reason NOT to mix the two types in the same DB if the particular table is not used with transactions?

    I'm reading different things about the efficiency of MyISAM, which is that for predominantly read-only, this engine can be faster. On the other hand, I also read that it's negligible.
  2. #2
  3. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,110
    Rep Power
    9644
    Yes, you can have both types. There's no particular reason why you should aim for consistency over engine features - I'd say the opposite is far more important.

    In general, MyISAM is faster for reads but isn't the best at concurrency, while InnoDB is a bit slower but is more reliable with operations.

    Want to explain what these different tables are and how they'll be used?
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    155
    Rep Power
    17
    Some tables contain data that describes a physical thing spatially, and these tables do not change unless we decide to add a location or change the layout / timing of an existing location - latitudes and longitudes, distances and bearings, relationships and known conflicts and such. Lots and lots of reads but very few writes, this is a key data store, so much so that we might replicate it in a cluster just for accessibility. There is an interface for creating and managing these tables, but all in all they just don't change that much. This first type would be fine as MyISAM especially if it speeded up reads.

    The second type of tables contain data that describe ever changing things and relationships of things that use the fairly static descriptions in the first set of tables They involve the transactions and they changes continually, and also must be error free (as much as possible with as much reading, writing, and competing use of resources), these tables describe things that are happening. I would use InnoDB for these. As well, I have a master table that keeps key information about activities and is used to check the "soundness" of other tables. There is some key data that is always correct (though derived from the ever changing activities) in this and one or two other tables, and they can be used to ferret out odds and ends in other tables that I will call "orphans". With this second type of table, I want to use both transactions to enforce error-free writes across several tables, and I also want to employ foreign keys. It's complicated, but it's not.

    I mean, there's lots of stuff that really doesn't change all that much but get's read a lot, like configuration setting that get loaded when the application fires up some process, or just tables of fairly static numbers, MyISAM should be excellent for these, don't need the overhead of InnoDB (or what I read here and there that there is more overhead with InnoDB...) Lots of reads, very few writes, less critical data.

    All of this is really the least of my problems, the biggest issue with the data is getting the tables "normalized" in a very efficient structure as far as the relationships. I've been spending a lot of time shaving microseconds off of blocks of code while still maintaining readability and serviceability, and I want my tables to reflect that "urgency" of efficiency. Let it not be said that they gave Arty Ziff carte blanche and he delivered a ****ing whale.

    I also keep hearing MySQL is for noobs, that Postgres is what "real programmers" use with "real applications". But for all I am doing that is fairly complex in the application, MySQL seems just fine for my minimal needs, it's a database driven application but the meat is in the algorithms that I've built in the code. I'm using PDO anyway, so if I get a wild hair and want to use something else or they decide to fit the application to some monster Oracle DB, it's possible anyway. At the end of the day, it's a "Scheduling Tool", not a "Planning Tool", so if it burps, nobody dies.
    Last edited by Arty Zifferelli; May 20th, 2017 at 02:56 AM.
  6. #4
  7. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,110
    Rep Power
    9644
    MyISAM for the first and InnoDB for the others sounds right to me.

IMN logo majestic logo threadwatch logo seochat tools logo