More powerful server... how much faster will my SQL queries be? - Irish SEO, Marketing & Webmaster Discussion

dude · #1 (**permalink**) 27-06-2007, 12:46 AM

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!

blacknight · #2 (**permalink**) 27-06-2007, 12:57 AM

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

dude · #3 (**permalink**) 27-06-2007, 01:29 AM

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

blacknight · #4 (**permalink**) 27-06-2007, 01:32 AM

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

dude · #5 (**permalink**) 27-06-2007, 01:52 AM

Thank you. I'll look into your suggestions.

Cheers.

hosting365 · #6 (**permalink**) 27-06-2007, 03:48 PM

More RAM always helps, and a bigger cache on the CPU will help any database functions.

blacknight · #7 (**permalink**) 27-06-2007, 04:16 PM

Quote:

Originally Posted by dude

Thank you. I'll look into your suggestions.

Cheers.

Here's a few links:

MySQL Optimization

Michele Neylon :: Pensieri Mobile Edition - there's some useful stuff in the comments

MySQL's Query Cache

dude · #8 (**permalink**) 27-06-2007, 05:25 PM

Great

Thanks again.

blacknight · #9 (**permalink**) 27-06-2007, 09:00 PM

Quote:

Originally Posted by dude

Great

Thanks again.

No problem - let us know how you get on

Dan · #10 (**permalink**) 05-07-2007, 12:58 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Script Installation, Server Administration	bizhat	Marketplace Offers	2	10-03-2008 11:29 AM