MySQL storage engine decision

Question

Hello,I am a web designer from the Netherlands. I am sort of new to building web apps from scratch, i have always used CMS, and customized them.

I am building a Auction website in php/mysql, which is finished. Recently i read a few articles about storage engines.

I have a few question that have been bothering me for a while now:

---

What sort of storage engine is best suited for my website, do i use myIsam, innodb, etc.

The website i am building is an auction website, it has to deal with several read and writes EVERY SECOND!! It has to check whether auctions are open, closed, paused, current price, etc.

I know not all tables in my database, will be accessed, these are only 3. They are currently all myIsam. Is this good? should i change them all? Change none? I really don't know what to do. Is i change them, will this impact my website drastically. And finnaly will these changes apply easily, or are they hard to do.

---

Another question i have is about Caching systems.

I read a few about them too but i don't know if they apply to the sort of website i have. The data i get out of the database changes a lot. Is it handy for me to cache them? For example an auction can be cached, because not all items change. But the auction wont be there forever, after a few hours its gone.

I can't see how a cache system could apply to that, or am i seeing this the wrong way. And again, if this system applies to me, will this change take a lot of effort, and will this effort make a huge amount of change.

---

These are all the questions i have, they are really general i guess, i hope some people can help me with them, i would really appreciate that.

Regards

Answer 1

InnoDB

On a scenario like the one you are describing I would go with InnoDB without hesitating. Take a look at these that you probably are going to need:

1. row level locking
2. multi-statement transactions
3. rollback

Caching

You've got several ways to do cache. But say your front-page gets 10 hits a second and changes every five minutes. Makes perfect sense to store the front-page in a pure html file and have a cron-job update that page every five minutes.

Answer 2

What sort of storage engine is best suited for my website, do i use myIsam, innodb, etc.

In general for any task default to InnoDB for its ACID-compliance and row-level locking. Consider resorting to MyISAM only for (a) fulltext search and (b) specific cases where performance under InnoDB isn't good enough (it's fairly rare that this is significant but some types of data can benefit from MyISAM's profile).

The website i am building is an auction website,

A function like auctions really really needs a consistent transactional database behind it because you're talking about people's money. MyISAM can't offer that. Your sites need to be using transactions for all non-atomic interactions, so that it's not possible for eg. two people to win the same auction when they bid at exactly the same time.

it has to deal with several read and writes EVERY SECOND!!

That's not a huge amount. However, InnoDB also typically performs better in write-biased access to a large table due to MyISAM's unfortunate table-level locking.

I read a few about them too but i don't know if they apply to the sort of website i have.

Don't rush into it. Some web authors seem to assume that memcache is going to be needed for any site to have decent performance but that really isn't the case. Any caching solution you apply will add complexity and potential failure points to deal with update times and now-outdated information.

So don't resort to caching until you really need to. If your common front-end pages habitually do complex, slow queries over the dataset then yes, having some sort of cache for that, whether that's in the database itself or an external solution like memcache, can improve speeds. But probably you'd actually be better off changing the schema and adding indexes to make that query cheaper than adding the complexity and inconsistency of a cache.

And on a site that's only doing “several” operations a second, you ain't gonna need it.