[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Query is not using Index
From:       Yogesh Kore <yogeshkore () gmail ! com>
Date:       2011-01-19 6:56:00
Message-ID: AANLkTinhUs-TN1cAphubjy5w4soL_O5yjypTzrqwtojP () mail ! gmail ! com
[Download RAW message or body]


Hi,

I am firing following query

SELECT
    'Sales' as transaction_type,
    CONCAT('$', SUM(CASE DATE(px_orders.sales_orders.order_completed_date)
    WHEN CURDATE() THEN px_orders.sales_order_products.paid_amount ELSE 0
END)) AS today,
    CONCAT('$', SUM(CASE WEEK(px_orders.sales_orders.order_completed_date,1)
    WHEN WEEK( CURRENT_TIMESTAMP(),1) THEN
px_orders.sales_order_products.paid_amount ELSE 0 END)) AS this_week,
    CONCAT('$', SUM(if (DATEDIFF(
DATE(px_orders.sales_orders.order_completed_date), DATE_SUB(CURDATE() ,
INTERVAL 7 DAY)  ) < '7' &&
DATEDIFF(DATE(px_orders.sales_orders.order_completed_date),
DATE_SUB(CURDATE() , INTERVAL 7 DAY) ) >= '0'
,px_orders.sales_order_products.paid_amount,0)))
    as week_1,
    CONCAT('$', SUM(if (DATEDIFF(
DATE(px_orders.sales_orders.order_completed_date), DATE_SUB(CURDATE() ,
INTERVAL 14 DAY)  ) < '7' &&
DATEDIFF(DATE(px_orders.sales_orders.order_completed_date),
DATE_SUB(CURDATE() , INTERVAL 14 DAY) ) >= '0'
,px_orders.sales_order_products.paid_amount,0)))
    as week_2,
    CONCAT('$', SUM(if (DATEDIFF(
DATE(px_orders.sales_orders.order_completed_date), DATE_SUB(CURDATE() ,
INTERVAL 21 DAY)  ) < '7' &&
DATEDIFF(DATE(px_orders.sales_orders.order_completed_date),
DATE_SUB(CURDATE() , INTERVAL 21 DAY) ) >= '0'
,px_orders.sales_order_products.paid_amount,0)))
    as week_3,
    CONCAT('$', SUM(if (DATEDIFF(
DATE(px_orders.sales_orders.order_completed_date), DATE_SUB(CURDATE() ,
INTERVAL 28 DAY)  ) < '7' &&
DATEDIFF(DATE(px_orders.sales_orders.order_completed_date),
DATE_SUB(CURDATE() , INTERVAL 28 DAY) ) >= '0'
,px_orders.sales_order_products.paid_amount,0)))
    as week_4,
    CONCAT('$', SUM(if (px_orders.sales_orders.order_completed_date >=
DATE_SUB(CURDATE( ),INTERVAL DAYOFMONTH(CURDATE( ))-1
DAY),px_orders.sales_order_products.paid_amount,0)))
    as mtd,
    CONCAT('$', SUM(if (px_orders.sales_orders.order_completed_date >=
DATE_SUB(CURDATE( ),INTERVAL DAYOFYEAR(CURDATE( ))-1
DAY),px_orders.sales_order_products.paid_amount,0)))
    as ytd
FROM
    px_orders.sales_order_products LEFT JOIN px_orders.sales_orders
    ON px_orders.sales_order_products.order_id = px_orders.sales_orders.id
WHERE
    px_orders.sales_order_products.status IN ( 'COMPLETED', 'CANCELED' )
AND px_orders.sales_orders.affiliate_organisation_id = 265;

By explaining this query I am finding that query is not using index in table
`px_orders.sales_order_products`. It is

Explain Output:-

      id                                 select_type  table      type
possible_keys key                       key_len   ref rows
Extra  1 SIMPLE sales_order_products
ALL   fk_op_order_id


159809 Using where  1 SIMPLE sales_orders eq_ref    PRIMARY PRIMARY
8  px_orders.sales_order_products.order_id 1 Using where
Explain is showing that table `sales_order_products` have possible key
fk_op_order_id but not using the key and examining all the rows from a
table.
order_id from sales_order_products is foreign key to id of sales_orders.

Anyone can tell why this is happening.? Is there a way to optimize this
query?

Thank You.


[prev in list] [next in list] [prev in thread] [next in thread]