Irish SEO,  Marketing & Webmaster Discussion

 

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

This is a discussion on More powerful server... how much faster will my SQL queries be? within the Server / Technical Administration Tips and Queries forums, part of the Webmaster Help category; Hello I've been looking into this and it looks like my initial table design is good, and my indexing is ...


Go Back   Irish SEO, Marketing & Webmaster Discussion > Webmaster Help > Server / Technical Administration Tips and Queries

Register Forum Rules FAQDonate Members List Calendar Search Today's Posts Mark Forums Read


Notices

Reply

 

LinkBack Thread Tools Display Modes
  #11 (permalink)  
Old 05-07-2007, 01:13 PM
Wannabe Geek
 
Join Date: Sep 2006
Posts: 193
Nominated 0 Times in 0 Posts
TOTW/F/M Award(s): 0
dude will become famous soon enough
Default

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.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #12 (permalink)  
Old 08-07-2007, 05:03 PM
jmcc's Avatar
Wannabe Geek
 
Join Date: Feb 2006
Posts: 311
Nominated 0 Times in 0 Posts
TOTW/F/M Award(s): 0
jmcc is just really nicejmcc is just really nicejmcc is just really nicejmcc is just really nice
Default

Quote:
Originally Posted by dude View Post
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.
What sizes are the tables and how complex are the queries? Have you chosen good indices?

Quote:
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.)
Again, a tweaked installation on a Celeron can outperform some higher spec machines that have not been optimised.

Quote:
If I were to upgrade to say... a Xeon 2.8 Ghz with 1 Gig ram, would my SQL queries run much faster?
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.

Quote:
Or is there a smarter way to speed up my queries?
Hard to give a definite answer without knowing the question.

Regards...jmcc

Last edited by jmcc; 08-07-2007 at 05:13 PM.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #13 (permalink)  
Old 12-08-2007, 12:47 AM
Coder
Recent Blog: Silver skies?
 
Join Date: May 2007
Location: Dublin
Posts: 50
Nominated 0 Times in 0 Posts
TOTW/F/M Award(s): 0
ConorP will become famous soon enough
Default

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.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #14 (permalink)  
Old 13-08-2007, 09:25 AM
paul's Avatar
ninja SEO
 
Join Date: Dec 2006
Location: .de
Posts: 1,118
Nominated 0 Times in 0 Posts
TOTW/F/M Award(s): 0
paul has much to be proud ofpaul has much to be proud ofpaul has much to be proud ofpaul has much to be proud ofpaul has much to be proud ofpaul has much to be proud ofpaul has much to be proud ofpaul has much to be proud ofpaul has much to be proud ofpaul has much to be proud of
Default

DRAM is cheap, why not go for something closer to 2GB ?
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #15 (permalink)  
Old 13-08-2007, 09:45 AM
blacknight's Avatar
Web Slave
Recent Blog:
 
Join Date: Jan 2006
Location: Ireland
Posts: 6,521
blacknight is a splendid one to beholdblacknight is a splendid one to beholdblacknight is a splendid one to beholdblacknight is a splendid one to beholdblacknight is a splendid one to beholdblacknight is a splendid one to beholdblacknight is a splendid one to behold
Send a message via ICQ to blacknight Send a message via AIM to blacknight Send a message via MSN to blacknight
Default

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!)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
Reply

Tags
faster, powerful, queries, server, sql

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump

Similar Threads

Thread Thread Starter Forum Replies Last Post
Script Installation, Server Administration bizhat Marketplace Offers 2 10-03-2008 12:29 PM


Sponsored links

Paid On Results


All times are GMT +1. The time now is 11:56 AM.


Powered by: vBulletin Version 3.7.3, Copyright ©2000 - 2008, Jelsoft Enterprises Limited.
Hosted in Ireland by Blacknight - Test your ISP |Irish Hosting Directory| Armchair.ie|Logo by Eden Web Design|Avatars by Afterglow |Latest Blog Entries | VPS HostingAd Management by RedTyger

Search Engine Friendly URLs by vBSEO 3.2.0