More powerful server... how much faster will my SQL queries be?

Status
Not open for further replies.
D

dude

New Member
Hello

I run a couple of websites which do complex "like '%word%'" SQL queries (such as this: Jobseeker - Jobs )

Some of my queries are a bit slow.

My server is a fairly basic celeron (can't remember the exact spec, but I think it's 1.8 Ghz with 512 MB ram.)

If I were to upgrade to say... a Xeon 2.8 Ghz with 1 Gig ram, would my SQL queries run much faster?

Or is there a smarter way to speed up my queries?

Any advice appreciated. Thanks!
 
mneylon

mneylon

Administrator
Staff member
Are you running the latest version of MySQL?

Have you optimised the setup?

Are you caching enough?

512MB of RAM doesn't sound like enough anyway ....
 
D

dude

New Member
Hi Blacknight

Thanks for the reply.

To answer your questions -

Are you running the latest version of MySQL?
MySQL version 4.0.27-standard. Pretty old I guess!

Have you optimised the setup?
No. I'm just running the queries. I could defo improve in this area...

Are you caching enough?
I don't think I'm caching at all...

Hmmm. Any advice?

Thanks
 
mneylon

mneylon

Administrator
Staff member
A newer version of mysql might help

You could also look into some of the caching / optimisation tips that you'll find online eg. caching the php could help with the overall performance

If you have a look at some of the MySQL blogs you should get some suggestions, though I'm sure someone on here will have suggestions
 
D

Dan

New Member
You'll know if you need more RAM by looking at your own server statistics. Are you swapping to disk at all?

Basic indexing can help hugely too - with MySQL you can log all your slow queries (say ones that took longer than 2 seconds to run) to a file and then use EXPLAIN to see how they are running.

So imagine your query is something like
Code: 
SELECT * FROM jobs WHERE Location like '%word%';
running an explain plan is as simple as putting EXPLAIN beforehand.

Code: 
EXPLAIN SELECT * FROM jobs WHERE Location like '%word%';

It'll give you some pointers as to whether its expecting an index, or doing something manky like creating a temporary table. Sometimes, due to the fact that MySQL does funny things internally with its optimiser, even rephrasing your queries to return the same results can help.

Lastly, if you are looking at full text searching mysql, its nasty. Take a look at Sphinx Search - its pretty good if you put a bit of work into it.
 
D

dude

New Member
Hello

I've been looking into this and it looks like my initial table design is good, and my indexing is good. The problem is basically that my database is huge, and the searches are huge.

I've always tried to limit the amount of unrelated results, so when you search for "customer care" I don't want "career" to be matched, but I do want "care,", " care ", ",care", "care." etc. So the searches are pretty complex.

And I can't index the body of the jobs (which is where the majority of the searches are aimed at.)

So I guess upgrading my server is one of my only options.

Ah well, it's about time I bought a server and colocated anyway, so I'll start looking into that. :)

Thanks everyone for your help.

PS Dan, I'm going to look into that Sphinx Search. Might be a possibility.
 
J

jmcc

Active Member
dude said:
Hello

I run a couple of websites which do complex "like '%word%'" SQL queries (such as this: Jobseeker - Jobs )

Some of my queries are a bit slow.
Click to expand...
What sizes are the tables and how complex are the queries? Have you chosen good indices?

My server is a fairly basic celeron (can't remember the exact spec, but I think it's 1.8 Ghz with 512 MB ram.)
Click to expand...
Again, a tweaked installation on a Celeron can outperform some higher spec machines that have not been optimised.

If I were to upgrade to say... a Xeon 2.8 Ghz with 1 Gig ram, would my SQL queries run much faster?
Click to expand...
The basic rule of thumb with databases is that RAM is often the bottleneck. If the temporary table in Mysql (if you are using myisam) grows too big for RAM, it will swap to harddisk. Run some of your queries and look at the processlist (show full processlist; ) and see if it swaps the tmp table to harddisk. Also time it if possible. That way you should get an idea of things.

Or is there a smarter way to speed up my queries?
Click to expand...
Hard to give a definite answer without knowing the question. ;)

Regards...jmcc
 
ConorP

ConorP

New Member
You might want to look at this http://www.day32.com/MySQL/tuning-primer.sh script and see if it can recommend anything for you to tune up.

However having done the whole run a web hosting company from one crappy celeron server, it sounds like it might be upgrade time. However I'd stay away from colo unless you have a really good idea of what you are doing and have no problem with a 3am trip into a dc.
 
mneylon

mneylon

Administrator
Staff member
I've upgraded my secondary machine to 4 gigs of RAM and this one is getting the same :) (once the bios decided to play nice!)
 
Status
Not open for further replies.
Top