Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Posts
    3

    Unanswered: MyISAM or InnoDB?

    Hi - I want to write a web based app with a database backend for my wife who works in the recruitment industry. I was wondering whether I should use InnoDB or MyISAM for my tables.

    MyISAM seems to have the ability to do a full text search while innodb does not. Innodb has the ability to maintain referential integrity while MyISAM does not.

    Which of these is more important? I was thinking that i could maintain referential integrity in myisam by checking that foreign key values being inserted really do exist via my php code. However, there seems to be no workaround for the lack of full text search in InnoDB.

    What are you thoughts? Please note that I really am new to databases - this will be my first ever database project. I'll post my database design seperately for feedback

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dubcat
    Which of these is more important?
    that's up to you

    as you point out, you can do the relational integrity checking with application code

    however, it's hard to do fulltext searching with application code

    how badly do you need to do fulltext searching? have you considered doing LIKE comparisons only?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    3
    well the full text search was going to be carried out on some free form fields where she can write notes for each discussion she has with a client. I was thinking maybe i can make the whole database in innodb and then just make the freeform text stuff in a sepearte table which is myisam..

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by dubcat
    I was thinking maybe i can make the whole database in innodb and then just make the freeform text stuff in a sepearte table which is myisam..
    In my opinion, that is the ideal approach. You get the integrity offered by InnoDB where you really need it, and you get the "flat file" features offered by MyISAM where you need them... Using the best features of the different storage engines to get the solution that best suits your needs seems to be as good as you can get to me!

    -PatP

  5. #5
    Join Date
    Jan 2007
    Posts
    3
    Perfect! Thanks so much for taking the time to reply.

    I hope i see you in the thread im going to make asking if my database design is sane I'm just going to spend another day making sure I have checked everything in triplicate before I post it up.

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    triplicate eh? I thought you said your wife worked in recruiting not the government. ;-)

  7. #7
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    When you might consider using MyISAM after all and would like to implement referential integrity, don't do that in the php code of the webpages. Write some triggers that do that for you, otherwise you might end up writing that same php code several times. But I agree that the InnoDB with one MyISAM for fulltext search is the way to go.

    Gr,
    Yveau

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


Posting Permissions

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