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

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

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

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

Thank you. I'll look into your suggestions.

Cheers.

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

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

Great Thanks again.

Originally Posted by dude

Great Thanks again.

No problem - let us know how you get on

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.