MySQL Forums
Forum List  »  NDB clusters

slow query - please help
Posted by: Tibor
Date: November 02, 2006 02:02AM

I am trying to move from InnoDB to Cluster (3 replicas) but one of my queries became horribly slow. It is the following query (selects the 50 best selling items from the last 3333 orders):

SELECT i_id, SUM(ol_qty) AS val FROM (SELECT o_id FROM orders ORDER BY o_date DESC LIMIT 3333) as recent_orders JOIN order_line ON (recent_orders.o_id = ol_o_id) JOIN item ON (ol_i_id = i_id) WHERE i_subject = "COOKING" GROUP BY i_id ORDER BY val DESC LIMIT 50;

This takes about 2.7s with NDBCluster as opposed to about 0.07s with InnoDB!
Why and what could I do to fix it?


Explain for NDBCluster:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3333
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: order_line
type: ref
possible_keys: PRIMARY,order_line_o_id,order_line_i_id
key: order_line_o_id
key_len: 4
ref: recent_orders.o_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: item
type: eq_ref
possible_keys: PRIMARY,item_i_subject,item_i_subject_i_title,item_i_subject_i_pub_date
key: PRIMARY
key_len: 4
ref: tpcw.order_line.OL_I_ID
rows: 1
Extra: Using where with pushed condition
*************************** 4. row ***************************
id: 2
select_type: DERIVED
table: orders
type: index
possible_keys: NULL
key: orders_date
key_len: 9
ref: NULL
rows: 77826
Extra:


Explain for InnoDB:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3333
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: order_line
type: ref
possible_keys: PRIMARY,order_line_o_id,order_line_i_id
key: PRIMARY
key_len: 4
ref: recent_orders.o_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: item
type: eq_ref
possible_keys: PRIMARY,item_i_subject,item_i_subject_i_title,item_i_subject_i_pub_date
key: PRIMARY
key_len: 4
ref: tpcw.order_line.OL_I_ID
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 2
select_type: DERIVED
table: orders
type: index
possible_keys: NULL
key: orders_date
key_len: 9
ref: NULL
rows: 77967
Extra: Using index


Thanks very much!

Tibor

Options: ReplyQuote


Subject
Views
Written By
Posted
slow query - please help
1353
November 02, 2006 02:02AM


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.