Efficient database search

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Efficient database search

Post 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?
User avatar
stereofrog
Forum Contributor
Posts: 386
Joined: Mon Dec 04, 2006 6:10 am

Post by stereofrog »

I think you will be better off with memcached
http://de3.php.net/memcache
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Neither my host nor my local set up have Memcache.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Use a heap table.
User avatar
stereofrog
Forum Contributor
Posts: 386
Joined: Mon Dec 04, 2006 6:10 am

Post by stereofrog »

superdezign wrote:Neither my host nor my local set up have Memcache.
:shrugs: find another host then.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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?
User avatar
kyberfabrikken
Forum Commoner
Posts: 84
Joined: Tue Jul 20, 2004 10:27 am

Re: Efficient database search

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Efficient database search

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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. ^_^
User avatar
kyberfabrikken
Forum Commoner
Posts: 84
Joined: Tue Jul 20, 2004 10:27 am

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
Post Reply