Re: [mv] MV4.0alpha11 mySQL stragnge behaviour

[Mike Heins <mike@minivend.com>]

******    message to minivend-users from Mike Heins <mike@minivend.com>     ******

Quoting Christian J. M|ller (cmueller@polinet.ch):
> ******    message to minivend-users from "Christian J. =?iso-8859-1?Q?M=FCller?=" <cmueller@polinet.ch>     ******
> 
> Mike Heins wrote:
> > 
> > ******    message to minivend-users from Mike Heins <mike@minivend.com>     ******
> > 
> > Quoting Christian J. M|ller (cmueller@polinet.ch):
> > > ******    message to minivend-users from "Christian J. =?iso-8859-1?Q?M=FCller?=" <cmueller@polinet.ch>     ******
> > >
> > > Installation is MV4.0alpha11 on Linux SuSE 6.3 with mySQL 23.8 alpha.
> > > I use the following query:
> > > [page href=scan arg="st=db/sq=select code,isbn,titel,autor,verlag,preis
> > > from products whrere suchkrit1='Architektur']architektur</A>
> > > As an initial step, I use only three [item-field ...] tags in the
> > > results page.
> > > This query returns 93 results. The display of the [item-field field]
> > > looks correct.
> > > My mySQL log, however, shows some 400 queries (!!) as a result. The
> > > entries are as follows:
> > >
> > > 24 Query      select * from products WHERE suchkrit1 = 'Architektur'  <-- my
> > > query
> > > 24 Query      select * from products where code = '2038510'           <-- MV
> > > 24 Query      select code from products where code = '2038510'
> > > 24 Query      select code from products where code = '2038510'
> > > 24 Query      select code from products where code = '2038510'
> > > 24 Query      select * from products where code = '2357615'
> > > 24 Query      select code from products where code = '2357615'
> > > 24 Query      select code from products where code = '2357615'
> > > 24 Query      select code from products where code = '2357615'
> > >
> > > and so on, four entries for each record found with the initial select
> > > query.
> > 
> > I will check on this, but the real answer is -- don't use [if-item-field ]
> > .... 8-)
> 
> I have not used [if-item-field] at all, only [item-field titel],
> [item-field autor] and [item-field preis] to display the result.

Then use [item-param preis], you put it as a return field in your select.

By the way, I made a couple of optimizations based on your report.

	1. removed record existence test when there is only one products file,
	   making [item-field name] same as to [item-data ProductFiles->[0] name]
	   in effect.
	2. fixed deficency where database_field called $db->record_exists
	   instead of $db->test_record

Both of those should greatly improve SQL performance. 

But the real key is to return the fields in the select and use [item-param name].
You can do this in MV searches as well:

	[area href=scan
			arg="
				rf=sku,price,description
				se=foo
				sf=bar
			"
	]

-- 
Mike Heins                          http://www.minivend.com/  ___ 
                                    Internet Robotics        |_ _|____
Any man who is under 30, and is     131 Willow Lane, Floor 2  | ||  _ \
not liberal, has not heart; and     Oxford, OH  45056         | || |_) |
any man who is over 30, and is not  <mikeh@minivend.com>     |___|  _ <
a conservative, has not brains.     513.523.7621 FAX 7501        |_| \_\
 -- Winston Churchill              
-
To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
Archive of past messages: http://www.minivend.com/minivend/minivend-list