SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    InnoDB vs MyISAM

    I was working under the assumption that only tables with foreign keys can be joined, and only InnoDB tables can have foreign keys. I'm now trying to unlearn everything I learned, but I'm still having a tough time figuring out which I should be using for my project - MyISAM tables, or InnoDB tables with foreign keys.

    If I (finally) understand correctly, MyISAM tables are easier to work with, load faster, aren't as liable to "choke up," and be easily backed up. InnoDB tables offer greater quality control and a few additional features, but they're a little more difficult to work with, are slower loading, and you have to buy expensive software to back them up. (However, couldn't you just copy the tables and publish them online, which would give you a back up?)

    So, question #1: Is what I wrote above accurate?

    #2: Which would you choose for my project, MyISAM or InnoDB? Or can I use a combination of the two?

    Actually, I'm working on two projects. The first is a series of tables focusing on continents, nations, states and natural regions (Great Plains, Great Basin, etc.). I'm not using it for a scientific laboratory or corporate use; I'm simply developing a series of educational websites.

    The biggest tables will probably be Nations (about 250 rows, half a dozen columns) and natural regions (perhaps 900 rows, half a dozen columns). But there will be dozens of related tables with information on area, population, government, etc.

    The second project focuses on animals. I'll simply have tables listing thousands of common and scientific names, diet, habitat, etc.

    I've seen evidence that supports the use of MyISAM and evidence that supports InnoDB. But I'm also pondering the possibility of using both. For example, I might make Continents my "master" table and give it an InnoDB designation. I could then designate other tables InnoDB... IF I want to give them a foreign key linked to Continents. If I don't plan on giving them a foreign key, I could make life simpler by designating them MyISAM.

    Any tips?

  2. #2
    SitePoint Member
    Join Date
    May 2004
    Location
    Stockholm Sweden
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The joining of tables has nothing to do with foreign keys. What foreign keys is doing is that you constraint a column that referes to another column so that it can only contain values that exist in the refered column. But yes, InnoDB supports foreign keys and MyISAM doesn't.

    The biggest difference between them is that InnoDB supports transactions and MyISAM doesn't.

    As for backup, as long as you can stop your database for a while when you run your backup then your fine. But if it has to be live during backup then it's another ball game and that is when the commercial backup comes in.

    You can combine them since when you create a table you define what type it should be (and if you don't define it then the default is MyISAM). But if you don't use transactions then I suggest you run with MyISAM and be happy with that.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    a lot of the decision as to what feature you need depends on who's going to be doing the updating

    if you want joe public entering data into your database, then relational integrity might be worth the effort

    if you are going to update the data yourself, use mysql

    and i gotta tell you, if you had mentioned earlier that you had the idea that you could only join by foreign/primary keys, i would certainly have put an emphatic stop to that line of thinking immediately

    oh wait, i did -- http://www.sitepoint.com/forums/show...72&postcount=2
    just remember that you can join tables on any columns you wish, regardless of whether those columns are primary/foreign keys
    i think perhaps you have been trying to do too much too fast and when you ask specific questions like "how can i do such-and-such with innodb tables" you get answers that address the question you asked, rather than answers which ask "why do you think you want to use innodb tables?"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "I think perhaps you have been trying to do too much too fast. . ."

    Amen! I created some pretty popular websites with FrontPage years ago and was getting as many as 20,000 hits a day. But I didn't realize how tough web design really is until I lost my job. I traded in FrontPage for Dreamweaver and started studying CSS. It took me over a year to get to the point where I felt I was ready to tackle PHP and MySQL.

    Now I'm desperately trying to get all my projects wrapped up and online before I 1) get trapped in a dead-end job, or 2) hit the streets. (I'm hoping to do a little freelance web design, eventually, if I can survive the competition.)

    But I've definitely learned to respect you PHP/MySQL gurus. You've obviously been doing this stuff for more than a year.

  5. #5
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    InnoDB also allows you to use two foreign keys as a primary key. MyISAM doesn't allow this AFAIK.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Hartmann
    MyISAM doesn't allow [you to use two foreign keys as a primary key] AFAIK.
    of course it does
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    of course it does
    hmmm... Everytime I've tried it has thrown errors, but not with InnoDB.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i guess your syntax must've been wrong

    here, feel free to borrow mine --
    Code:
    create table FOO
    ( id tinyint not null primary key auto_increment
    , name varchar(9)
    );
    insert into FOO ( name ) 
    values ( 'curly' ), ( 'larry' ), ( 'moe' )
    ;
    create table BAR
    ( id tinyint not null primary key auto_increment
    , name varchar(9)
    );
    insert into BAR ( id, name ) 
    values ( 101, 'dog' )
         , ( 102, 'cat' )
    ;
    insert into BAR ( name ) 
    values ( 'fish' )
         , ( 'gerbil' )  
    ;
    create table FUBAR
    ( FOOid tinyint not null 
    , BARid tinyint not null 
    , foreign key ( FOOid ) references FOO ( id )
    , foreign key ( BARid ) references BAR ( id )
    , primary key ( FOOid, BARid )
    );
    insert into FUBAR
    values ( 1, 101)
         , ( 1, 102)
         , ( 1, 104)
         , ( 3, 102)
         , ( 3, 104)
    ;
    
    select FOO.name as Stooge
         , BAR.name as Pet
      from FOO
    inner
      join FUBAR
        on FOO.id = FUBAR.FOOid
    inner
      join BAR
        on FUBAR.BARid = BAR.id
    order
        by 1,2                 
        
    Stooge  Pet
    curly   cat
    curly   dog
    curly   gerbil
    moe     cat
    moe     gerbil
    although i must say, it's kinda moot, since myisam tables cannot enforce referential integrity anyway
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    So since MyISAM tables can't enforce the referential integrity what's the point of doing the code that you posted?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    to demonstrate that you can have two foreign keys as a primary key, which you thought myisam tables didn't allow
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What i understand (could be wrong):
    INNODB supports cascading etc while MyISAM doesn't.

    Also, I seem to remember reading somewhere in the MySQL manual that INNODB is limited to 1000 records or something. (although i just had a look now and couldnt find that again...so it is probibly horrible lies on my behalf)


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
  •