[ic] very slow (20 seconds) form search (MV.4.6.3)

[Mike Heins]

Quoting sda sda (cafedvd@yahoo.com):
> 
> --- Mike Heins <mikeh@minivend.com> wrote:
> > Quoting sda sda (cafedvd@yahoo.com):
> > > When I used the following form search,
> > > 
> > > <FORM ACTION="[area search]">
> > > <INPUT TYPE=hidden NAME=mv_searchtype VALUE=sql>
> > > <select name="mv_search_field">
> > > <option value="title" selected> Titles
> > > <option value="actor">Cast</select>
> > > <INPUT MAXLENGTH=30 NAME=mv_searchspec type=text
> > > size=10>
> > > <INPUT TYPE=hidden NAME=mv_column_op VALUE=rm>
> > > 
> > > 
> > > 
> > > the response time is almost 20 seconds using
> > mysql.
> > > When I checked the log file in mysql,
> > > I noticed that it had  'SELECT * FROM products'
> > > line that is responsible for the delay.
> > > Why is minivend generating this sql command?
> > > Is this a bug? Or, is there another way to
> > > search using form inputs?
> > > 
> > > I have 36000 items in products.
> > 
> > How is it supposed to do a full-text search without
> > looking at every
> > record, pray tell?
> 
> Shouldn't the query contain 'WHERE' clause such as:
> 
>   SELECT * FROM products WHERE title LIKE "%foo%"
> 
> instesd of the time consuming query below?
> 
>   SELECT * FROM products
> 

Sure, you can do that. But which SQL will you honor? The ones that
tolerate no %, the ones that won't match with % at the beginning of the
string if that word begins the string, or the ones that do? The ones
that require UPPER() for a case-insensitive match? The ones that have
a regex-capable LIKE?

Interchange supports at least 15 different SQL implementations as well
as non-SQL tables. That is why that needs to be implemented by the person
who knows what SQL they are using. There is no SQL-92 standard for that.
I wish there was -- I would do something with it.

-- 
Red Hat, Inc., 131 Willow Lane, Floor 2, Oxford, OH  45056
phone +1.513.523.7621 fax 7501 <mheins@redhat.com>

Light travels faster than sound. This is why some people appear bright
until you hear them speak. -- unknown