[RESOLVED] Speed/Usability Test: Recipe Database

[atheimer]

I've got a site that's much larger than my normal sites and I'm wondering
what specs I should really have to make this thing usable. You'll notice that initial recipe searches take a long time however I can't seem to speed them up on the current system. I'd love to hear your thoughts both on that issue as well as overall usability.

Current Server Specs:
Dotster Linux Plesk VPS w/ 256mb RAM, 500gb Month Transfer, 10gb storage
Php 5
Mysql 5

[ Link removed per OP request ]

Example of Slow Query🙁This executes super-fast on my local wamp stack which is why I suspect the low vps hardware to be the problem.)

SELECT recipes.title, recipes.url, recipes.description,recipes.id,sources.name, recipes.meta, match (title) against (' breast ') as relevance FROM recipes Inner Join sources ON recipes.sid = sources.id WHERE match (title) against ('breast')>0 HAVING relevance>0 ORDER BY relevance DESC LIMIT 0,30

Love to hear thoughts on the site as well as the speed.
Thanks!

[cgraz]

You can probably drop the WHERE clause since you take care of that with the HAVING clause. A few questions:

1. How long is this query taking to run on your production server vs. the live server?
2. What are the specs on your production server?
3. What type of traffic are you receiving on the site? Does this query run significantly faster when there are little to no visitors on the site?
4. Do you have both recipes.sid and sources.id indexed?
5. You said your table is big. How many records do you consider big?
6. Post an EXPLAIN statement so we can see what MySQL Optimizer is doing.

256MB of ram is fairly low, but it also depends on how busy your server is. The first thing to do is make sure your query is property optimized and your tables are well designed and indexed. If that looks good, then you should consider looking at your hardware IMO.

[Roger_Ramjet]

256Mb is nothing for a database server so move to a server with 2Gb+ for a start.