|
|
|
On Sat, Jul 03, 1999 at 01:57:49PM -0400, George Knerr wrote:
> ****** message to minivend-users from "George Knerr" <gknerr@webalution.net> ******
>
> Dear Mr. Christopher F. Miller,
>
> Thank you very much for the encouraging reply. This site is magnitudes
> bigger and more complex than anything I have done thus far. They want it up
> by Jan. 1 2000. That is of coarse if anything is up ;).
>
> Is there any added difficulty that you are aware of with implementing Oracle
> or Sybase with minivend?
You don't have the source. They are bound to be harder. I've not
had cause to use either yet with minivend. <understatement>The hard part
is probably covered in the readme for DBD::Sybase or DBD::Oracle.</understatement>
>
> If you could be so kind as to be a little more verbose with the following
> comment you made:
>
> >Our usual product
> > page in typical catalog will frequently have at least one
> > 3-5 table multi-database JOIN. Poorly indexed that's 11 seconds,
> > properly indexed .10 seconds in mySQL.
>
> Specifically, what do you mean by "3-5 table multi-database JOIN" and what
> in your opinion is an example of a poorly indexed db versus one that cuts
> down searches by a magnitude of ~100 as in your example.
This is a typical "history" in a category drill down,
4 tables in 2 databases:
$query=qq`SELECT DISTINCT classifieds.lcc.cc,classifieds.lcc.local_category,classifieds.lsc.sc,classifieds.lsc.local_sub_category FROM aux,classifieds.lcc,classifieds.lsc,classifieds.sc WHERE aux.code=$v{item_code_quoted} AND aux.cscc=classifieds.sc.sc AND classifieds.lcc.cc = classifieds.sc.cc AND classifieds.lsc.sc = classifieds.sc.sc AND classifieds.lsc.author = '$lsc_author' AND classifieds.lcc.author = classifieds.lsc.author`;
In catalog we build this past Friday, this particular
query changed 11 sec to .10 when we got around to indexing
product (product name). About 5k items I think:
$query=qq`SELECT products.code,products.price,products.product,products.product_sub,products.description,products.text,products.thumbnail,products.image,aux.cscc,aux.in_stock,aux.unit_price,aux.units FROM products,aux WHERE products.product LIKE '[item_field product]' AND products.code=aux.code`;
No, I'm not going to detail exactly what those are doing! ;^>
Both above in mySQL over a network (as opposed to UNIX socket)
on vanilla pentium machines.
The optimizations in a brand name db can't make much of a dent;
product catalogs are relatively simple SQL queries.
>
> Thanks again,
>
> George Knerr
> gknerr@webalution.net
>
--
Christopher F. Miller, Publisher cfm@maine.com
MaineStreet Communications, Inc 208 Portland Road, Gray, ME 04039
1.207.657.5078 http://www.maine.com/
Database publishing, electronic commerce, office and internet integration.