Efficient database search
Moderator: General Moderators
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Efficient database search
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?
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?
- stereofrog
- Forum Contributor
- Posts: 386
- Joined: Mon Dec 04, 2006 6:10 am
I think you will be better off with memcached
http://de3.php.net/memcache
http://de3.php.net/memcache
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
- stereofrog
- Forum Contributor
- Posts: 386
- Joined: Mon Dec 04, 2006 6:10 am
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
- kyberfabrikken
- Forum Commoner
- Posts: 84
- Joined: Tue Jul 20, 2004 10:27 am
Re: Efficient database search
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.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.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: Efficient database search
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.kyberfabrikken wrote: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.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.
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.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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.
I'm not sure exactly what your doing but if you just need a performance boost that should do it.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Sounds good to me. ^_^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.
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?
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
That's sensible. Okay then. ^_^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.
- kyberfabrikken
- Forum Commoner
- Posts: 84
- Joined: Tue Jul 20, 2004 10:27 am
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.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
It's just a search through a database table.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.