Re: Optimizing ORDER BY any ideas?

[Marc Van Olmen]

Hi jay,
thx again!

The query:

EXPLAIN select
DISTINCT SAO_INDEX
from
T_SALES_ORDER_ITEM,
T_PROGRAM_PRODUCT,
T_PROPERTY,
T_SCHEDULE_ITEM,
T_SALES_ORDER
where
SOI_LNK_PRODUCT NOT IN (7,8,9)
and PMP_LNK_CHANNEL = 1
and PMP_LNK_PROGRAM = 1
and SOI_LNK_PRODUCT = PMP_LNK_PRODUCT
and SOI_LNK_SALES_ORDER = SAO_INDEX
and PRY_LNK_SALES_ORDER_ITEM = SOI_INDEX
and SCI_LNK_SALES_ORDER_ITEM = SOI_INDEX
and SCI_LNK_MEDIUM IN (1,2)
and SCI_LNK_CHANNEL = 1
and ( ((SCI_START_DATE <= '2004-09-08') and ( SCI_FINISHED_DATE >= '2004-09-08' ))
OR ((SCI_START_DATE >= '2004-09-08') and (SCI_FINISHED_DATE <= '2005-09-06'))
OR (SCI_START_DATE between '2004-09-08' and '2005-09-06'))

ORDER BY PRY_EXPORT_CITY

This query takes about 3 seconds, and around 0.3 second without the order by

I'm using mysql 5.0b12 when I add \G after the last character, phpadmin reports an error 1064, maybe something wrong with current version of mysql.

-----------------------------------
id: 1
select_type: SIMPLE
table: T_PROPERTY
type: index
possible_keys: PRY_LNK_SALES_ORDER_ITEM
key: PRY_LNK_SALES_ORDER_ITEM
key_len: 4
ref: NULL
rows: 5537
Extra: Using index; Using temporary; Using filesort
-----------------------------------
id: 1
select_type: SIMPLE
table: T_SCHEDULE_ITEM
type: ref
possible_keys: SCI_FINISHED_DATE,SCI_START_DATE,SCI_LNK_CHANNEL,SCI_LNK_SALES_ORDER_ITEM,SCI_LNK_MEDIUM
key: SCI_LNK_SALES_ORDER_ITEM
key_len: 4
ref: tvplanner4.T_PROPERTY.PRY_LNK_SALES_ORDER_ITEM
rows: 4
Extra: Using where
-----------------------------------
id: 1
select_type: SIMPLE
table: T_SALES_ORDER_ITEM
type: eq_ref
possible_keys: SOI_INDEX,SOI_LNK_PRODUCT,INDEX_LNK_SOA_PRODUCT
key: SOI_INDEX
key_len: 4
ref: tvplanner4.T_SCHEDULE_ITEM.SCI_LNK_SALES_ORDER_ITEM
rows: 1
Extra: Using where
-----------------------------------
id: 1
select_type: SIMPLE
table: T_SALES_ORDER
type: eq_ref
possible_keys: SAO_INDEX
key: SAO_INDEX
key_len: 4
ref: tvplanner4.T_SALES_ORDER_ITEM.SOI_LNK_SALES_ORDER
rows: 1
Extra: Using index
-----------------------------------
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; Distinct

so this is what I'm getting back

marc