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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
