Re: Very slow dependent subquery
Thanks
1/ "If you really have more than 1GB (total) of RAM please state how much"
I have 1GB RAM available in "Windows" sense. It is the amount that is given on the Windows Task Manager.
I tried however to lower these 3 values but got no significant changes.
2/ "Please provide SHOW CREATE TABLE"
CREATE TABLE `customer` (
`CODE_CLIENT` varchar(20) NOT NULL,
KEY `IDX_CODE_CLIENT` (`CODE_CLIENT`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
CREATE TABLE `member` (
`EMAIL` varchar(255) DEFAULT NULL,
`DATEJOIN` date DEFAULT NULL,
`CODE_CLIENT` varchar(20) DEFAULT NULL
KEY `IDX_CODE_CLIENT` (`CODE_CLIENT`) USING BTREE,
KEY `IDX_CODE_CLIENT_DATEJOIN` (`CODE_CLIENT`,`DATEJOIN`) USING BTREE,
KEY `IDX_CODE_CLIENT_EMAIL` (`CODE_CLIENT`,`EMAIL`) USING BTREE,
KEY `IDX_EMAIL` (`EMAIL`) USING BTREE,
KEY `IDX_EMAIL_CODE_CLIENT` (`EMAIL`,`CODE_CLIENT`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8$$
CREATE TABLE `kbx_kiloutou_purchase` (
`EmailAdress` varchar(255) DEFAULT NULL,
`Date` date DEFAULT NULL,
`AmountPaid` double DEFAULT NULL,
KEY `IDX_EmailAdress` (`EmailAdress`) USING BTREE,
KEY `IDX_EmailAdress_Date` (`EmailAdress`,`Date`) USING BTREE,,
KEY `IDX_Date_EmailAdress` (`Date`,`EmailAdress`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8$$
I have created extra indexes for test purpose. They are clearly redundant yet this should not change execution time?
3/ "Are the keys wider than they need to be? "
I have no idea on how to change that.
For instance, field `EMAIL` of table`member` is VARCHAR(255) and associated index key_len is 768. eg, should I index it only on first 30 chars? would it change key_len ?
4/"Possible keys ... Smells like redundancy"
Indeed. please see 2/
5/ "purchase has 15M records...Does the query need to touch all the rows?"
The insert is only 300K rows touching maybe 8 out of 15 millions rows. Yet, 2hours to scan 8M rows seems long !
6/"SELECT m.CODE_CLIENT, SUM(p.AmountPaid)..." :
d 1
select_type SIMPLE
table p
type ALL
possible_key IDX_EmailAdress,IDX_EmailAdress_Date
key NULL
key_len NULL
ref NULL
row 15335853
extra Using temporary; Using filesort
d 1
select_type SIMPLE
table m
type ref
possible_key IDX_EMAIL,IDX_EMAIL_CODE_CLIENT
key IDX_EMAIL
key_len 768
ref p.EmailAdress
row 1
extra Using where
This appears quite long too.
Edited 1 time(s). Last edit at 09/25/2013 12:56AM by patrick canarelli.