Re: Optimizing ORDER BY any ideas?

[Marc Van Olmen]

hi Jay,

Maybe something got confused (for one I used phpadmin and other terminal, and they seem to give different results... maybe because \G... not certian why) and thanks for still trying to get answered my question,
I did reimport my database again today and I forgot if I optimized a few indexes manually on table structure. So to be certain I created the explain of both queries through the terminal.

Here below is the results I got: and they look similar to me now.

Reading through the info here below. I noticed that in the last row, it is using "index" in the case I don't have "ORDER BY".




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

*************************** 1. 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_CHANNEL
key_len: 4
ref: const
rows: 16
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: T_SALES_ORDER_ITEM
type: ref
possible_keys: SOI_INDEX,SOI_LNK_SALES_ORDER,SOI_LNK_PRODUCT
key: SOI_LNK_PRODUCT
key_len: 4
ref: tvplanner4.T_PROGRAM_PRODUCT.PMP_LNK_PRODUCT
rows: 414
Extra:
*************************** 3. row ***************************
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
*************************** 4. row ***************************
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_SALES_ORDER_ITEM.SOI_INDEX
rows: 8
Extra: Using where; Distinct
*************************** 5. 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_SCHEDULE_ITEM.SCI_LNK_SALES_ORDER_ITEM
rows: 1
Extra: Using where; Distinct
5 rows in set (0.17 sec)


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'))

*************************** 1. 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_CHANNEL
key_len: 4
ref: const
rows: 16
Extra: Using where; Using temporary
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: T_SALES_ORDER_ITEM
type: ref
possible_keys: SOI_INDEX,SOI_LNK_SALES_ORDER,SOI_LNK_PRODUCT
key: SOI_LNK_PRODUCT
key_len: 4
ref: tvplanner4.T_PROGRAM_PRODUCT.PMP_LNK_PRODUCT
rows: 414
Extra:
*************************** 3. row ***************************
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
*************************** 4. row ***************************
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_SALES_ORDER_ITEM.SOI_INDEX
rows: 8
Extra: Using where; Distinct
*************************** 5. 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_SCHEDULE_ITEM.SCI_LNK_SALES_ORDER_ITEM
rows: 1
Extra: Using where; Using index; Distinct
5 rows in set (0.01 sec)