[ic] Faster search with mysql

[Mike Heins]

Quoting Peter Jakl (peter@jakl.net):
> Is there any way to get the value of "mv_sql_query" passed directory to the
> SQL server? I want to be able to speed up the search against 120,000
> products. I don't want to use the [query] tag on the results page since I
> will lose interchanges ability to cache products and use the results paging
> feature.
> 
> In all attempts, in using mv_sql_query, interchange still passes "select *
> from products" and then filters the list based on the criteria itself. I can
> speed up the search significantly if interchange would just send the
> mv_sql_query directly. It looks as if this was the intent and could really
> provide a benefit, but is not working for me.

You cannot pass it directly. There are two ways to do this faster.

If one of your search criteria is an "eq", and it is a coordinated search,
Interchange will start the search with "select * from products where foo =
'bar'". Then it operates on that set to filter better.

Second, you *can* use the query tag and do results paging.

    [query  list=1
	    sql="select * from products where foo = 'bar'"
	    more=1
	    ml=10
	    ]
	[list]
	    [sql-code] -- [sql-param description] -- [sql-param price]
	[/list]
	[more-list]
	    More matches:
	    [more]
	[/more-list]
    [/query]

If Interchange was closely tied to one or two specific databases we could
use their various regex features, but they are too fragmented for that
to work. The search parser would be a nightmare -- wait, if you have looked
at the code, it already is! 8-)

-- 
Red Hat, Inc., 3005 Nichols Rd., Hamilton, OH  45013
phone +1.513.523.7621      <mheins@redhat.com>

Experience is what allows you to recognize a mistake the second
time you make it. -- unknown