List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:April 27 2007 4:32pm
Subject:Optimize code?
View as plain text  
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.

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
          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*************************** 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
          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



Thread
Optimize code?Jerry Schwartz27 Apr
  • Re: Optimize code?Jay Pipes28 Apr
    • RE: Optimize code?Jerry Schwartz30 Apr