Professional Web Applications Themes

which storage engine should I use- MyISAM or InnoDB? - MySQL

I've read articles and postings about MyISAM vs InnoDB but I am still a bit unsure about which storage engine to use for my new project. I am developing a website in PHP/MySQL, which includes features such as member login, insert/update/delete operations for members to maintain their records, report generation based on database entries, shopping cart but payments will be made through Paypal gateway and the search facility. This is a medium level website. What storage engine is best suited for this site and why? I prefer InnoDB because of foreign key support. I'd also like to know why MyISAM ...

  1. #1

    Default which storage engine should I use- MyISAM or InnoDB?

    I've read articles and postings about MyISAM vs InnoDB but I am still a
    bit unsure about which storage engine to use for my new project.

    I am developing a website in PHP/MySQL, which includes features such as
    member login, insert/update/delete operations for members to maintain
    their records, report generation based on database entries, shopping
    cart but payments will be made through Paypal gateway and the search
    facility.

    This is a medium level website. What storage engine is best suited for
    this site and why? I prefer InnoDB because of foreign key support. I'd
    also like to know why MyISAM is not made to support foreign keys? If
    you guys recommend MyISAM for me, how should I implement foreign key
    support? Do I have to explicitely make provisions for foreign key
    support in MyISAM using PHP?

    Thanks

    crescent_au@yahoo.com Guest

  2. #2

    Default Re: which storage engine should I use- MyISAM or InnoDB?

    com wrote: 

    you need to look at what makes each engine unique:

    myisam -- fast for reading and inserting, table level lock which makes
    it not suitable for transactions
    innodb -- fast for updating and most suitable for transactions because
    it uses row level locks

    you certainly can use a combination of these 2 types of engines in your
    database.

    with innodb, you'll have cascading deletes/updates; however with myisam,
    you'll have to do it yourself in the code or in stored procedures. so
    maybe some of your tables should be innodb while others should be myisam.

    hope this helps.
    lark Guest

  3. #3

    Default Re: which storage engine should I use- MyISAM or InnoDB?

    I would recommend InnoDB because it is ACID compliant , thus very
    needed for your site (you want to implement all-or-nothing bank-style
    behaviour right?).

    However, I noticed that InnoDB tables get a bit slower than MyISAM
    tables during bulk-inserts ,especially of TEXT/Blobs and Varchars. It
    is all relative. It depends what fields you want to store, how many
    writes / reads and so on.

    However, InnoDB is not the universal solution to any solution. You will
    probably end-up using both types, especially if you write some log
    information, which is not of critical importance.
    Hope it helps.




    Dragomir Stanchev
    http://www.linkedin.com/in/dragomirstanchev
    http://www.student.informatik.tu-darmstadt.de/~stanchev/CV%20deutsch.pdf

    The|Godfather Guest

Similar Threads

  1. MyISAM vs. InnoDB
    By jinxidoru in forum MySQL
    Replies: 5
    Last Post: October 22nd, 05:07 PM
  2. Dubbio problema InnoDB - MyIsam
    By Plaguebreath in forum MySQL
    Replies: 2
    Last Post: October 16th, 09:37 AM
  3. InnoDB VS MyISAM
    By howachen@gmail.com in forum MySQL
    Replies: 2
    Last Post: July 10th, 11:12 AM
  4. MyISAM VS InnoDB VS Memory
    By howachen@gmail.com in forum MySQL
    Replies: 0
    Last Post: June 11th, 02:07 PM
  5. change storage engine
    By somebody in forum MySQL
    Replies: 3
    Last Post: May 11th, 08:16 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139