[ic] Multiple ProducFiles, MySQL, and performance

Thomas J.M. Burton interchange-users@icdevgroup.org
Tue Mar 11 21:22:00 2003


Hello IC Users,

One of the catalogs we run is set up with 12 product tables (this is
done because the products in each category need completely different
fields for the product data).

The catalog is running on IC 4.8.6 using a MySQL database.

I was recently watching the server's MySQL log and noticed that when an
item's flypage is requested, it results in a mass of queries like the
following:

502045 Query  select sku from books where sku = 'Taiyo80100spi'
502045 Query  select sku from cdplayers where sku = 'Taiyo80100spi'
502045 Query  select sku from computer where sku = 'Taiyo80100spi'
502045 Query  select sku from micspreamps where sku = 'Taiyo80100spi'
502045 Query  select sku from media where sku = 'Taiyo80100spi'
502045 Query  select description from media where sku = 'Taiyo80100spi'
502045 Query  select sku from books where sku = 'Taiyo80100spi'
502045 Query  select sku from cdplayers where sku = 'Taiyo80100spi'
502045 Query  select sku from computer where sku = 'Taiyo80100spi'
502045 Query  select sku from micspreamps where sku = 'Taiyo80100spi'
502045 Query  select sku from media where sku = 'Taiyo80100spi'
502045 Query  select category from media where sku = 'Taiyo80100spi'

...and so on for about 120 lines total.

Now, I know why it's doing this and have read in the IC docs about how
the ProductFiles will be searched in order as specified in the config
directive.

My question is, what can I do to optimize this and reduce the number of
queries when a flypage is accessed?

The docs mention using [item-data table ...] to bypass this behavior,
but I'm not sure how I'd accomplish that in a flypage since I'd need to
dynamically change the table being referenced. I suppose I could build
a different flypage to be used with each category, but I'd rather not
go through the trouble if there's an easier way.

I've also tried using

    [timed-build minutes="20" file="timed/flypage_[item-code]"]
    ... Flypage HTML/ITL ...
    [/timed-build]

but it doesn't seem to stop the queries.

Any suggestions?

Thanks in advance,
Tom

___________________________________
Thomas J.M. Burton
Global Focus Digital Media, LLC
www.globalfocusdm.com