Hi Jerry, comments inline....
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.
> 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.
> 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
>
>
>
>
