Page 1 of 2

Efficient database search

Posted: Wed Jul 18, 2007 1:04 pm
by superdezign
So running a LIKE %query% search every time a user goes from one page of the same search to the next seems very wasteful to me, so what I'm thinking of doing is storing search results in a database and having them be deleted upon expiration (like, 30 minutes maybe). However, I'm wondering wht would be a good way to keep track of the search that the user is currently browsing. For basic searching, I could simply take a look at the query through the query string, but for advanced searches, I don't want to put all of the options into the query string so it doesn't look messy. I've considered placing the id of the search into the query string, but I don't ever see id numbers in search engine query strings.

I think I could use a session to do this, but I'd like to be able to allow the user to run a different query without having to see the old results when they go to the search page. If it comes down to it, I may just do that anyway.

Any suggestions?

Posted: Wed Jul 18, 2007 1:14 pm
by stereofrog
I think you will be better off with memcached
http://de3.php.net/memcache

Posted: Wed Jul 18, 2007 1:21 pm
by superdezign
Neither my host nor my local set up have Memcache.

Posted: Wed Jul 18, 2007 1:26 pm
by Benjamin
Use a heap table.

Posted: Wed Jul 18, 2007 1:32 pm
by stereofrog
superdezign wrote:Neither my host nor my local set up have Memcache.
:shrugs: find another host then.

Posted: Wed Jul 18, 2007 1:35 pm
by superdezign
astions wrote:Use a heap table.
I'm reading into heap tables and am curious as to how they work exactly. Should I create a separate memory heap table for each set of search results, or store them all into the same one with an identifier for each set?

Re: Efficient database search

Posted: Wed Jul 18, 2007 1:38 pm
by kyberfabrikken
superdezign wrote:I've considered placing the id of the search into the query string, but I don't ever see id numbers in search engine query strings.
That's because it's a bad idea. If you do it that way, people won't be able to bookmark a search, since it would expire after 30 minutes. If a search has many options, you will get long URL's. That's just how it is.

Re: Efficient database search

Posted: Wed Jul 18, 2007 1:43 pm
by superdezign
kyberfabrikken wrote:
superdezign wrote:I've considered placing the id of the search into the query string, but I don't ever see id numbers in search engine query strings.
That's because it's a bad idea. If you do it that way, people won't be able to bookmark a search, since it would expire after 30 minutes. If a search has many options, you will get long URL's. That's just how it is.
Okay then, long query string it is. And, I'll use a compacted version of the query string as the identifier for the search I save in the database.

I was originally going to save searches as huge serialized arrays, but temporary tables seem to be the way to go. I'm just not sure if I should be creating and dropping tables, or just create one central table to handle them all. I'm very iffy about giving my database user create and drop privileges.

Posted: Wed Jul 18, 2007 1:48 pm
by John Cartwright
If you want to go that route, I would go with your "central" table to handle this. I could get really nasty if you are generating temporary tables.

Posted: Wed Jul 18, 2007 1:51 pm
by Benjamin
Well adding the ability to bookmark them makes it a little more tricky. Basically you could use 1 heap table (ram table) to cache results temporarily. You'll have to keep it clean because queries fail when it gets full. If someone requests a url that isn't in the heap table, you'll have to query your main table and place it back into the heap table.

I'm not sure exactly what your doing but if you just need a performance boost that should do it.

Posted: Wed Jul 18, 2007 1:52 pm
by superdezign
Jcart wrote:If you want to go that route, I would go with your "central" table to handle this. I could get really nasty if you are generating temporary tables.
Sounds good to me. ^_^

Thinking on it, I feel that this table will begin to fill up quickly, so do you think 30 minutes is too long to wait before removing the data?

Posted: Wed Jul 18, 2007 1:58 pm
by John Cartwright
Assuming your using mysql, I wouldn't worry too much about you table filling up considering you can handle million and million of rows before you experience any kind of slowdown. I would probably do something like 48 hours minimum before the search expired.

Posted: Wed Jul 18, 2007 2:04 pm
by superdezign
Jcart wrote:Assuming your using mysql, I wouldn't worry too much about you table filling up considering you can handle million and million of rows before you experience any kind of slowdown. I would probably do something like 48 hours minimum before the search expired.
That's sensible. Okay then. ^_^

Posted: Wed Jul 18, 2007 2:50 pm
by kyberfabrikken
What kind of search are you doing? Instead of caching results, maybe you could use an indexing-engine. I used Zend_Search_Lucene recently, and I'm quite impressed.

Posted: Wed Jul 18, 2007 2:58 pm
by superdezign
kyberfabrikken wrote:What kind of search are you doing? Instead of caching results, maybe you could use an indexing-engine. I used Zend_Search_Lucene recently, and I'm quite impressed.
It's just a search through a database table.