MySQL Forums
Forum List  »  Optimizer & Parser

Re: slow selects
Posted by: Toa Sty
Date: September 01, 2006 08:16AM

>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.

Options: Reply• Quote


Subject
Views
Written By
Posted
3145
August 31, 2006 08:02AM
2090
August 31, 2006 08:43AM
2077
August 31, 2006 09:06AM
2084
September 01, 2006 03:04AM
2025
September 01, 2006 04:17AM
2299
September 01, 2006 04:50AM
2028
September 01, 2006 07:24AM
Re: slow selects
2079
September 01, 2006 08:16AM
2068
September 02, 2006 12:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.