I cannot optimize query even though I join over the key
Posted by:
Sinan Ozel
Date: October 29, 2005 06:26AM
Here is my query:
SELECT
*,
DATE_FORMAT( last_call_date, '%d / %m / %Y' ) AS last_call_date,
DATE_FORMAT( cr_customer_stats.last_stock_exit_date, '%d / %m / %Y' ) AS latest_transaction_date
FROM
st_parts
LEFT JOIN cr_routine_sales USING( brand, part_no, casing )
LEFT JOIN cr_customers ON( cr_customers.customer_code = cr_routine_sales.customer_code )
LEFT JOIN cr_customer_stats USING(customer_code)
LEFT JOIN st_stocks ON( st_stocks.brand = st_parts.brand && st_parts.part_no = st_stocks.part_no && st_parts.casing = st_stocks.casing )
WHERE
( ( size != 'Büyük' ) || ISNULL(size) ) && ( ( ( ( quantity + po_quantity ) > sales_quantity ) && cr_customer_stats.last_stock_exit_date < ( CURRENT_DATE - INTERVAL (sales_period_days+next_call_days) DAY ) ) || ( ( ( quantity + po_quantity ) <= sales_quantity ) && cr_customer_stats.last_stock_exit_date < ( CURRENT_DATE - INTERVAL (sales_period_days+next_call_days-(6*7)) DAY ) ) ) && ( ( cr_customers.archive != 1 ) || ISNULL(cr_customers.archive) )
And here is my problem:
It takes too long to run! When I run the DESCRIBE query, I found out the following:
"cr_customers, ALL, PRIMARY, 3772, range checked for each record (index map: 1)"
First time I see it. What does it mean, does anyone know?
I'll really appreciate if anybody could help.
Best Regards,
Sinan Ozel
Subject
Views
Written By
Posted
I cannot optimize query even though I join over the key
1728
October 29, 2005 06:26AM
1236
October 29, 2005 01:41PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.