MySQL
I’m having constant problems with MySQL, it is very slow now, especially when there are dozens of search bots parsing my sites.
I also found it very annoying that simple subqueries take a lot of time to execute - it works about 10 times faster after I separate them and use two queries instead of one, passing the data from first to second via PHP script.
It appears to be a well-known problem for MySQL 4.1 - I’m not sure if that’s still happening with 5.0 version - but I don’t have too much time to make thorough tests, so I’m going to move to PostgreSQL, as it seems to be a much better solution. Right now my database code is not too complex (yet), so it won’t take long.
Any comments?
P.S. I understand that this move also means ideology change - things should be done with stored procedures, foreign keys, triggers and so on - so part of business logic is moving to DB. But actually this is exactly how it should be, IMHO.
Update. I’m seriously thinking about IBM’s DB2…
This entry was posted
on Tuesday, February 21st, 2006 at 10:20 and is filed under website creation 
, database , dedicated , virtual dedicated server , db .
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.
February 21st, 2006 at 12:37
Why, oh why people don’t know about DB2? Please read my post here: http://regul8or.blogspot.com/2006/02/blog-post.html
February 21st, 2006 at 12:40
I have the easiest method - you can ban those spiders, that annoying you
February 21st, 2006 at 12:46
regul8or - you’re totally right. My colleague now said me that he used it in the past (about 8 years ago) and its performance is much higher than today’s MySQL. Will look into it. First I want to add a nice database layer that will allow me to change database provider seamlessly…
February 21st, 2006 at 12:49
I’m working with DB2 for years and it’s the best database ever. And I used to use a whole lot of them
February 21st, 2006 at 12:49
Lab: well, I can ban them, but only Google brings 60% of traffic to my sites
February 21st, 2006 at 12:50
If you will need help with DB2 on Windows - just ask me directly. Don’t know about Linux, though.
February 21st, 2006 at 12:53
regul8or - well, now I know the right person to disturb when I have DB2 questions
February 26th, 2006 at 09:33
Oh, I feel your MySQL pain. In my experience—and I suspect few have more similar problems—the thing that gets you is large GROUP BYs and ORDER BYs. LibraryThing uses no subselects. You can usually do everything with a complex JOIN. Some of the joins make my head hurt, but they still execute very quickly. Having a fast server helps. My old Linux jail just couldn’t handle the load.
February 26th, 2006 at 10:29
You’re totally right about GROUP BY and ORDER BY things. The point is, switching to DB2 should (more or less) automatically ‘fix’ those bottlenecks for me, I just don’t have enough time to sit and test all possible solutions for MySQL to work properly… I need a server that works fast ‘out of the box’ and DB2 seems to be the right thing.