Re: SQL performance

[mikeh@minivend.com]

******    message to minivend-users from mikeh@minivend.com     ******

Quoting mikeh@minivend.com (mikeh@minivend.com):
> 
> Here is what I propose to do:
> 
> 	1. Change the [sql type=list ....] to return a hash-based
> 	reference. This will be easy to do, I think, but there is 
> 	a possibility of some incompatibilities when making queries
> 	of databases that have multiple identical keys. I think it
> 	might be possible to default to the old behavior with a
> 	flag in that case.

Prepare for a rant.

I looked at the code, and I did this a long time ago. Have
any of the people having problems with speed been using:

	[sql type=list
		 query="select code,category,title,price from products" ]

Category:	[sql-param 1]<BR>
Title:		<A HREF="[area [sql-code]]"> [sql-param 2] </A><BR>
Price:		<A HREF="[area order [sql-param 0]]"> [sql-param 3] </A><BR>

[sql-param 0] is nothing but a synonym for [sql-code].

The difference should be dramatic; perhaps I could give it named-field
access though how to do that doesn't seem obvious to begin with.

I WONDERED how I managed to search 500,000-record Oracle databases
and get almost instantaneous results while other people are suffering.

I can't believe that no one has come up with this; here is the example
in the docs for sql type=list:

  <TABLE BORDER=2>
  <TR><TH><B>SKU</B></TH><TH><B>Description</B></TH><TH><B>Price</B></TH>
  [sql type=list
    query="select * from arbitrary where code > '19' order by field2"]
  <TR>
    <TD>[page [sql-code]][sql-code]</A></TD>
    <TD>[sql-param 1]</TD>
    <TD>[sql-param 2]</TD>
  </TR>
  [/sql]
  </TABLE>

This is especially powerful when you consider a joined query
like

	SELECT code, price, title, extended.desc
	FROM   products, extended
	WHERE  products.category = 'Renaissance'

By the way, the same thing works for searches if you use the
rf=field1,field2,field3 thing; and there it accepts the name
as well as the number.

(
	I know someone will ask this. This cannot be the default for
	[sql-data ...]	or [sql-field ...] -- this is obvious when
	you think about it. How are you going to get access to other
	databases? If you do a joined query you can get the fields you
	want back via [sql-param ...]; if you don't want to mess with
	it for small lists or occasional data items you can use the
	MV accessor.
)

As to the previous method, my test case selected 1000+ records from
15,000 with 3 [sql-data fields] in under 3 seconds in MySQL.

This tells me people are not indexing their databases.  If you are
using an SQL database it is YOUR responsibility to index the fields --
if MiniVend is taking literally FOREVER to return stuff that tells me
you didn't index your 'code' field. If you are not indexing at least
the 'code' field, then the results are on your head. If you want
fast searches, you must also index the rest of the fields you might
be searching.

-- 
Mike Heins                          http://www.minivend.com/  ___ 
                                    Internet Robotics        |_ _|____
"The U.S. Senate -- white           131 Willow Lane, Floor 2  | ||  _ \
male millionaires working           Oxford, OH  45056         | || |_) |
for YOU!" -- Dave Barry             <mikeh@minivend.com>     |___|  _ <
                                    513.523.7621 FAX 7501        |_| \_\
-
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