[ic] Misc Questions

[Brian Kosick <briank@nacs.net>]

Hello All,
	I'm in the process of building a site, and I have a few questions, that I 
couldn't find answers that would help me on the mailing list archives, or 
the docs.

Here's my setup.  RaQXTR -- 933MHz PIII, 256RAM, IC 4.7.5, raid5.  Which 
connects (VIA 100MB switch) to a postgres 7.0.2 db server that has a 733 
MHz PIII, 512RAM, raid5, etc.  My products file has just under 1000 items 
(955 to be exact) and a zip_zone table that has 963 entries. I have created 
index's for code, zones columns on the products table, and an index for 
zone on the zip_zone table.

I have to perform a complex search, and I have working SQL code for either 
a sub-query, or a JOIN type search.  Both work.

Here's my code:

[comment]
[query list=1 st=db more=1 ml=50 sql=|
         SELECT products.code, products.name, products.short_desc,
         products.price, products.qty, products.new_plant, products.avail
         FROM products, zip_zone
         WHERE zip_zone.zip = '[value search_zip]'
         AND products.zones ~ zip_zone.zone
         ORDER BY products.name;
         |
]
[/comment]

[query list=1 more=1 ml=50 st=db sql=|
         SELECT code, name, short_desc, price, qty, new_plant
         FROM products
         WHERE zones ~ (
                 SELECT zone from zip_zone
                 WHERE zip = '[value search_zip]');
         |
]

[list]
<snip html taken out>
[sql-param code] [sql-param name] etc.
</snip>
[/list]
[more-list]
[more]
[/more-list]
[/query]

Questions:

1)  This search takes anywhere from 60 sec to over 2 minutes to return the 
page.  Is there something that I'm missing that can speed this up?  Do I 
need to add something like "DATABASE products INDEX code"  to the catalog.cfg?

1a)  I added a more list, which increased display time, BUT

2)  The More List displays at the top of the table, is there a way to make 
it appear at just the bottom?  Or at both top AND bottom of the table?

here's a link to the site for the curious:
http://dev.bluestoneperennials.com/cgi-bin/bluestone.cgi

Brian Kosick
Web Programmer
New Age Consulting Service, Inc.
216-619-2000
briank@nacs.net