[ic] st=db

[Dan Browning]

* Ed LaFrance <edl at newmediaems.com> [2003-11-12 13:59]:
> At 12:13 PM 11/12/2003 -0800, you wrote:
> >Why wouldn't you want every loop search to use st=db to search the database
> >instead of the txt source file?  The txt file is only up to date when it 
> >has
> >just been exported from the db file, right?  It seems like leaving st=db 
> >out
> >of a loop search would send you searching through old data most of the 
> >time.
> >I'm noticing that happening now that I've upgraded to mySQL, but maybe it
> >was happening before too.
> >
> >- Grant
> 
> Ironically, you may get better performance out of those text-file searches 
> now, since in most cases interchange applies a [loop search..] to SQL by 
> doing a 'select * from table' to grab a copy of the entire table contents, 
> essentially importing it and scanning it locally. This is not very 
> efficient to say the least, and can kill your catalog's performance with 
> large tables.
> 
> The best answer at this time is to start replacing interchange-type 
> searches with SQL passed via the [query] tag; well-written queries and 
> judicious indexing of SQL tables will give you impressive performance 
> gains. Rewriting the code for the keyword search box and the standard 
> foundation catalog results pages, sidebar links code (bar_link routine), 
> components which utilize search code, etc., is also well worth the effort 
> once you have comitted to SQL, IMHO.

For the sake of completeness, one more alternative is to add 
"co=1/rf=*/op=eq" to your searches.  The addition of those three frequently 
reduces the actual query from
	
	SELECT * FROM products
	
	to
	
	SELECT * FROM products WHERE category='Hammers'

However, any search feature that relies on Interchange or Perl (such as 
regular expressions) will take you back to the "SELECT * from products".

HTH,
-- 
Dan Browning, Kavod Technologies, <db at kavod.com> 360.843.4074x217
6700 NE 162nd Ave, Ste 611-210, Vancouver, WA.    Random Fortune:
Simon's Law:
	Everything put together falls apart sooner or later.