>Thanks the reason I used innodb was for row level locking....
>which from what I understand was much faster...
Yeah and no. To be honest it's too complicated a subject to simply say that it's "much faster". It really depends on your workload.
MyISAM is brilliant if you only have simple and fast selects/inserts/updates running and _never_ have anything that's going to take a long time to run. (Also it's not great from an integrity POV after crashes etc etc but I guess your app might not be _too_ worried about this, I dunno)
I have a website session DB which runs brilliantly on MyISAM and often runs at 250 replaces or updates /sec and 250 selects/sec concurrently. I'm pretty sure it could support much more than this too. (probably 5 times, I dunno) The trick being that all statements only affect one row so finish almost instantaneously. Table locks are held for such a tiny amount of time that they never really interfere with other queries.
Anyway there's lots info on the differences around the web.
http://www.mysqlperformanceblog.com/ is a good place to start (though it's often pretty advanced)
To be honest though I think that changing from InnoDB to MyISAM might be a pretty drastic action for you to take at this stage. Unless you're a bit careful (or experienced) MyISAM can treat you a bit badly sometimes so I'd only consider changing as a last resort.
Also, you'll almost certainly only see a fixed percentage performance change by moving from innodb to myisam whereas optimising a query can give you 100* better query peformance sometimes.
So let's see how that index change has helped :)
Toasty
PS I'm off on hols now so won't be able to post for a week or so. Hopefully someone else on here will pick this up for you. I'm sure there are load of people here who can sort this out easily.
Edited 1 time(s). Last edit at 09/01/2006 09:17AM by Toa Sty.