[ic] search parameters for SQL

saa at concision.com saa at concision.com
Fri Oct 22 11:32:32 EDT 2004

Out of the box the "small_search_box" component specifies it's search
as follows:

      <INPUT TYPE=hidden NAME=mv_searchtype VALUE=db>
      <INPUT TYPE=hidden NAME=mv_matchlimit VALUE=[control matches 10]>
      <INPUT TYPE=hidden NAME=mv_sort_field VALUE=category>
      <INPUT TYPE=hidden NAME=mv_search_field VALUE="*">
      <input type=hidden name=mv_substring_match value=1>
      <INPUT MAXLENGTH=30 NAME=mv_searchspec type=text size=8>

We changed the value for mv_search_field like this:

      <INPUT TYPE=hidden NAME=mv_search_field VALUE="category">

We imported our 100,000+ line products.txt file into an SQL
(postgresql) table. It all worked. But we had a performance
problem. We turned on debugging and saw that interchange was sending
this sql query to the SQL server when we did a search from the
"small_search_box" component:

    select * from products where inactive != 1;

It then proceeded to fetch all 100,000+ rows from our products table
before proceeding. It took about 10 seconds. Where could this
come from? It couldn't have been from the search coded in
"small_search_box" because we clearly specified that the search field
was "category" and that would have shown up in the "where" clause.

To make a long, teeth gnashing, hair pulling story short, this query,
was produced by the search coded in "small_search_box" and the
mv_search_field value was inexplicably ignored.

This seems to me to be a bug. Does anyone have another explanation?
If nothing else, perhaps we can have this tale included in the FAQ
under "I am using SQL, and Interchange is slow ..."

BTW, after more trial and error we came up with this workaround which
required replacing

      <INPUT TYPE=hidden NAME=mv_search_field VALUE="category">
      <INPUT MAXLENGTH=30 NAME=mv_searchspec type=text size=8>


      <INPUT TYPE=hidden NAME=mv_like_field VALUE="category">
      <INPUT MAXLENGTH=30 NAME=mv_like_spec type=text size=8>
      <INPUT TYPE=hidden NAME=mv_min_string VALUE="0">

Note in particular that setting mv_min_string to 0 was required
because Interchange was apparently still trying to utilize the value
of mv_searchspec even though mv_searchspec wasn't even specified.

More information about the interchange-users mailing list