Re: Optimizing ORDER BY any ideas?

[Marc Van Olmen]

Jay,

sorry about the confusion I was thinking you wanted the other big query,

ok here the Explain /G of the original query with ORDER BY:


*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: T_SALES_ORDER_ITEM
type: range
possible_keys: SOI_INDEX,SOI_LNK_PRODUCT
key: SOI_LNK_PRODUCT
key_len: 4
ref: NULL
rows: 3337
Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: T_PROGRAM_PRODUCT
type: ref
possible_keys: PMP_LNK_PROGRAM,PMP_LNK_CHANNEL,PMP_LNK_PRODUCT
key: PMP_LNK_PRODUCT
key_len: 4
ref: tvplanner4.T_SALES_ORDER_ITEM.SOI_LNK_PRODUCT
rows: 2
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: T_PROPERTY
type: eq_ref
possible_keys: PRY_LNK_SALES_ORDER_ITEM
key: PRY_LNK_SALES_ORDER_ITEM
key_len: 4
ref: tvplanner4.T_SALES_ORDER_ITEM.SOI_INDEX
rows: 1
Extra:

This query takes about 0.3 to execute


There below the one without the ORDER BY:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: T_SALES_ORDER_ITEM
type: range
possible_keys: SOI_INDEX,SOI_LNK_PRODUCT
key: SOI_LNK_PRODUCT
key_len: 4
ref: NULL
rows: 3337
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: T_PROGRAM_PRODUCT
type: ref
possible_keys: PMP_LNK_PROGRAM,PMP_LNK_CHANNEL,PMP_LNK_PRODUCT
key: PMP_LNK_PRODUCT
key_len: 4
ref: tvplanner4.T_SALES_ORDER_ITEM.SOI_LNK_PRODUCT
rows: 2
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: T_PROPERTY
type: eq_ref
possible_keys: PRY_LNK_SALES_ORDER_ITEM
key: PRY_LNK_SALES_ORDER_ITEM
key_len: 4
ref: tvplanner4.T_SALES_ORDER_ITEM.SOI_INDEX
rows: 1
Extra:



Thanks for your input again,

marc