Thanks, Jay.
Comments interspersed...
> Jerry Schwartz wrote:
> > I need (ultimately) to update some prices in a prod_price
> table. First, I
> > need to locate a product and its associated prices using a field
> > prod.prod_price_prod_id which is not unique, and is often
> null, but it is
> > indexed. (Subsequent operations will use PRIMARY keys, but
> I haven't gotten
> > there yet.) I further qualify a product by an associated
> pub.pub_code, to
> > weed out possible duplicate prod_pub_prod_id entries from different
> > publisher.
>
> Good... I would move to lookups/joins on a primary key ASAP
> for performance.
I don't see where I have any choices about the keys that I use. Every table
uses a primary key of the form "tablename_id", and is linked to at least one
other table (usually prod) by a field storing the primary key of the other
table. So prod_price has a primary key of prod_price_id, and has a field
prod_id that links it back to the prod table. The relationship of prod to
prod_price is 1:many. The link between pub and prod is similar, with the
relationship between pub and prod also being 1:many.
>
> > My SELECT statement is
> >
> > SELECT SQL_CALC_FOUND_ROWS prod.prod_num,
> > prod_price.prod_price_end_curr,
> > prod_price.prod_price_end_price,
> > prod_price.prod_price_disp_curr,
> > prod_price.prod_price_disp_price
> > FROM pub JOIN prod JOIN prod_price
> > WHERE pub.pub_id = prod.pub_id
> > AND pub.pub_code IN ("ener","fit","govt","heal","id","life","manu")
> > AND prod.prod_id = prod_price.prod_id
> > AND prod.prod_pub_prod_id = "101771"
> > AND prod_price.prod_price_disp_curr = 'USD'
> > AND prod_price.prod_price_end_curr = 'USD';
> >
> > An EXPLAIN of this query looks pretty good:
> >
> > *************************** 1. row ***************************
> > id: 1
> > select_type: SIMPLE
> > table: prod
> > type: ref
> > possible_keys: PRIMARY,pub_id,prod_pub_prod_id
> > key: prod_pub_prod_id
> > key_len: 766
>
> Whoooaaahhh.... is it really a 766-byte-wide key? That's
> going to kill you.
I don't know where that 766 is coming from, the actual field is a
varchar(15) for all of the keys.
>
> > ref: const
> > rows: 2
> > Extra: Using where
> > *************************** 2. row ***************************
> > id: 1
> > select_type: SIMPLE
> > table: pub
> > type: eq_ref
> > possible_keys: PRIMARY
> > key: PRIMARY
> > key_len: 45
>
> Same here. 45-byte-wide PK is a killer.
>
> > ref: giiexpr_db.prod.pub_id
> > rows: 1
> > Extra: Using where
> > *************************** 3. row ***************************
> > id: 1
> > select_type: SIMPLE
> > table: prod_price
> > type: ref
> > possible_keys: prod_id
> > key: prod_id
> > key_len: 46
>
> Same
>
> > ref: giiexpr_db.prod.prod_id
> > rows: 2
> > Extra: Using where*************************** 1. row
> > ***************************
> > id: 1
> > select_type: SIMPLE
> > table: prod
> > type: ref
> > possible_keys: PRIMARY,pub_id,prod_pub_prod_id
> > key: prod_pub_prod_id
> > key_len: 766
>
> Same
>
>
> > ref: const
> > rows: 2
> > Extra: Using where
> > *************************** 2. row ***************************
> > id: 1
> > select_type: SIMPLE
> > table: pub
> > type: eq_ref
> > possible_keys: PRIMARY
> > key: PRIMARY
> > key_len: 45
> > ref: giiexpr_db.prod.pub_id
> > rows: 1
> > Extra: Using where
> > *************************** 3. row ***************************
> > id: 1
> > select_type: SIMPLE
> > table: prod_price
> > type: ref
> > possible_keys: prod_id
> > key: prod_id
> > key_len: 46
> > ref: giiexpr_db.prod.prod_id
> > rows: 2
> > Extra: Using where
> >
> > As you can see, if first retrieves the (possibly multiple)
> prod records
> > based upon the prod_pub_prod_id, which is keyed. Then it
> hops over to the
> > pub table using the common pub_id field, which is the
> PRIMARY key in the pub
> > table, so it can check my IN condition. Finally, it picks
> up (possibly
> > multiple) prod_price records using the common field prod_id.
> >
> > The optimization seems pretty good. A single execution of
> this query, using
> > the CI MySQL, is reported to take .05 seconds.
> Unfortunately, I have about
> > 20,000 products to process; so at a minimum I would expect
> it to take 1,000
> > seconds. Even ignoring the overhead from PHP, this is going
> to run for
> > awhile.
> >
> > Does anyone have any suggestions for improving my code?
> >
> > Regards,
> >
> > Jerry Schwartz
> > Global Information Incorporated
> > 195 Farmington Ave.
> > Farmington, CT 06032
> >
> > 860.674.8796 / FAX: 860.674.8341
> >
> >
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
