[SOLVED] 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

 

 

URL removed

[lightningstrike]

First of all I would recommend creating a page system to limit the number of results a page.

 

Also providing us information as to what database you use e.g. Oracle, MSSQL, MySQL, mSQL, SQLite, Access, etc.

 

Also whether the database is on an external server.

 

Edit: It appears that you are crawling other websites. Perhaps you should use a caching system to record the results from them into your own database to increase speed on popular searches. If possible check if it is possible to get a faster connection to crawl the other websites faster.

 

Personally I find it to be quite slow. But I'm curious as to why a user couldn't simply search the larger databases such as recipezaar, food network etc that your reference.

 

I think finding unindexed recipes in static html pages would be better to link to than already existing search able recipe databases.

[torb]

Caching is definetly required here... It loads quite slowly, at least for me.

[Technex]

Slow but loads. 30-40 secs.

[atheimer]

What sort of caching would you suggest?  I do believe I'm caching on the mysql side but I might be missing something somewhere else.  Also, since I added my paging function I've noticed that only page 1 loads super slow.  After that hitting the next button runs an almost identical query in no time.  Is this the mysql caching in action?

 

Thanks!

 

 

Caching is definetly required here... It loads quite slowly, at least for me.

[lightningstrike]

Yes, that would probably be memcache kicking in on the second page.

 

However do you crawl other databases and store the links in a database beforehand. Or do you crawl only when the user types the keywords in?

[atheimer]

All processing is done beforehand, so the query is only to the db.  I suspect there's a big hangup in

my use of the MATCH clause as this is the first time I've used it vs. LIKE.

 

Basically in pseudo it works like this:

1. User enter terms

2. Php function breaks down, cleans, and converts boolean phrases into mysql 5 friendly context.

3. Query is built

4. query runs

5. results returned.

 

I've been reviewing several php spider/search scripts to learn how to improve the search.  Here is a normal query + the explain.

 

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

 

Takes roughly 15 seconds to execute on my local machine.

Here's the explain:

 

+----+-------------+---------+----------+-----------------+----------+---------+

--------------------+------+-----------------------------+

| id | select_type | table  | type    | possible_keys  | key      | key_len |

ref                | rows | Extra                      |

+----+-------------+---------+----------+-----------------+----------+---------+

--------------------+------+-----------------------------+

|  1 | SIMPLE      | recipes | fulltext | source,searchme | searchme | 0      |

                    |    1 | Using where; Using filesort |

|  1 | SIMPLE      | sources | eq_ref  | PRIMARY        | PRIMARY  | 4      |

recipe.recipes.sid |    1 |                            |

+----+-------------+---------+----------+-----------------+----------+---------+

--------------------+------+-----------------------------+

2 rows in set (0.00 sec)

 

Anything else I should look at?

 

[atheimer]

test