MySQL Forums
Forum List  »  Optimizer & Parser

Re: Very slow dependent subquery
Posted by: patrick canarelli
Date: September 25, 2013 12:53AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2616
September 18, 2013 03:06AM
1540
September 18, 2013 07:06AM
1481
September 18, 2013 07:51AM
1454
September 18, 2013 02:57PM
1375
September 18, 2013 02:13PM
1277
September 18, 2013 03:10PM
1314
September 24, 2013 10:04PM
Re: Very slow dependent subquery
1640
September 25, 2013 12:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.