SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    MyISAM or InnoDB?

    I know this is a question that comes up a lot, but I have a question pertaining to my specific database usage, if you don't mind?

    At present we use all MyISAM tables and every query comes via a web-server (PHP mysql commands). However, since the system was originally developed, the number of JOINs that we use has vastly increased. They're effectively being used as FKs in a number of tables, but they're all done in the query.

    So, is it worth switching to InnoDB and using proper FKs? What kind of performance impact should I expect? The current database is only about 200MB in size, but holds 2.2 million records in 92 tables (wow, hadn't realised there were that many until just now!). I'm fortunate enough that we're moving site and I've been allowed to make big changes to the system, which has allowed me to fix design mistakes that have been made over the years, and do it properly. We'll be starting the data from scratch so all of that data above won't be coming with us, but we are expecting a lot of data to go in to the database as we're increasing capacity at the site.

    Thanks all

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    foreign keys won't get you any measurable advantages because your application code is (presumably) already written to handle invalid relationships -- but they wouldn't hurt, either

    innodb uses row locks instead of table locks, so performance of inserts, updates, and deletes might improve markedly

    if you use any fulltext indexing, those tables would have to remain myisam
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No, we don't use fulltext indexing. So, with row locks, instead of table locks, if for example, one query is taking a long time, another query will still be able to work on that same table?

    As for the FKs vs code, yes, the code is already written to deal with invalid relationships, but there's a lot of new code coming as a result of me fixing long-standing DB design issues (like having an id PK on a job table, when the job number should be the PK and no id is required) so I'll be rewriting a lot of code anyway


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •