Query is not using Index

--20cf30433ee89b1083049a2d577c
Content-Type: text/plain; charset=ISO-8859-1

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.

--20cf30433ee89b1083049a2d577c--