Re: Optimizing ORDER BY any ideas?

[Marc Van Olmen]

Hi Jay.

Side question:
How you come to the conclusion between index *scan* and seek, explained in your book?

Anyway I remember now that I added before an index for T_PROPERTY but with the new import I forgot. So I added it now like you suggested but it doesn't show much difference:

Create Table: CREATE TABLE `T_PROPERTY` (
`PRY_PHOTO` char(80) default NULL,
`PRY_LNK_CREATE_USER` int(11) NOT NULL,
`PRY_LNK_IMMO_PACKET` int(11) default NULL,
`PRY_LNK_IMMO_OFFER` int(11) default NULL,
`PRY_LNK_MODIFIED_USER` int(11) NOT NULL,
`PRY_MODIFIED_DATE` datetime NOT NULL,
`PRY_CREATE_DATE` datetime NOT NULL,
`PRY_LNK_SALES_ORDER_ITEM` int(11) NOT NULL,
`PRY_CONTACT` char(80) default NULL,
`PRY_DESCRIPTION` varchar(1624) default NULL,
`PRY_INDEX` int(11) NOT NULL auto_increment,
`PRY_EXPORT_TELETEXT` varchar(1624) default NULL,
`PRY_MUSICTEXT` varchar(1624) default NULL,
`PRY_PRINT_INTRODUCTION` varchar(1624) default NULL,
`PRY_EXPORT_MUSICTEXT` varchar(1624) default NULL,
`PRY_PRINT_TITLE` varchar(1624) default NULL,
`PRY_PRINT_FINAL_TEXT` varchar(1624) default NULL,
`PRY_PRINT_FINAL_SMALL` varchar(1624) default NULL,
`PRY_TELETEXT` varchar(1624) default NULL,
`PRY_EXPORT_ADDRESS` char(80) default NULL,
`PRY_LNK_PROPERTY_STATUS` int(11) default NULL,
`PRY_EXPORT_TYPE` char(80) default NULL,
`PRY_EXPORT_LAYOUT` varchar(1624) default NULL,
`PRY_EXPORT_CITY` char(80) default NULL,
`PRY_EXPORT_VISITINFO` varchar(1624) default NULL,
`PRY_SOLD_DATE` date default NULL,
`PRY_LNK_PROPERTY_SALES_LABEL` int(11) default NULL,
`PRY_EXPORT_SALES_COMMENT` char(80) default NULL,
`PRY_CONTACT_PHONE` char(80) default NULL,
`PRY_SMS` varchar(512) default NULL,
UNIQUE KEY `PRY_INDEX` (`PRY_INDEX`),
KEY `PRY_PHOTO` (`PRY_PHOTO`),
KEY `PRY_SOLD_DATE` (`PRY_SOLD_DATE`),
KEY `PRY_LNK_PROPERTY_SALES_LABEL` (`PRY_LNK_PROPERTY_SALES_LABEL`),
KEY `PRY_LNK_SALES_ORDER_ITEM_2` (`PRY_LNK_SALES_ORDER_ITEM`,`PRY_EXPORT_CITY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


Now the queries again first the

NO ORDER BY


*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: T_PROPERTY
type: index
possible_keys: PRY_LNK_SALES_ORDER_ITEM_2
key: PRY_LNK_SALES_ORDER_ITEM_2
key_len: 245
ref: NULL
rows: 5626
Extra: Using index; Using temporary
*************************** 2. 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_PROPERTY.PRY_LNK_SALES_ORDER_ITEM
rows: 4
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: T_SALES_ORDER_ITEM
type: eq_ref
possible_keys: SOI_INDEX,SOI_LNK_SALES_ORDER,SOI_LNK_PRODUCT
key: SOI_INDEX
key_len: 4
ref: tvplanner4.T_SCHEDULE_ITEM.SCI_LNK_SALES_ORDER_ITEM
rows: 1
Extra: Using where
*************************** 4. 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
*************************** 5. 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; Distinct


The query itself takes 0.1 seconds


with ORDER BY

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: T_PROPERTY
type: index
possible_keys: PRY_LNK_SALES_ORDER_ITEM_2
key: PRY_LNK_SALES_ORDER_ITEM_2
key_len: 245
ref: NULL
rows: 5626
Extra: Using index; Using temporary; Using filesort
*************************** 2. 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_PROPERTY.PRY_LNK_SALES_ORDER_ITEM
rows: 4
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: T_SALES_ORDER_ITEM
type: eq_ref
possible_keys: SOI_INDEX,SOI_LNK_SALES_ORDER,SOI_LNK_PRODUCT
key: SOI_INDEX
key_len: 4
ref: tvplanner4.T_SCHEDULE_ITEM.SCI_LNK_SALES_ORDER_ITEM
rows: 1
Extra: Using where
*************************** 4. 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
*************************** 5. 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; Distinct

The Query itself takes 0.9 seconds

T_PROPERTY,T_SALES_ORDER_ITEM,T_SALES_ORDER each have less then 6000 records, T_PROGRAM_PRODUCT has 4 records and T_SCHEDULE_ITEM has about 40000 records not huge numbers. I'm running on a Dual 2.5ghz G5 processor.


We are infact are back now to the initial reason why I started this query because I was surprised that the query took so long because I indexed PRY_EXPORT_CITY initialy