sql request too long ... How to simplify?

Question

i would like to reduce the process time of my SQL request (actually it runs 10 minutes ...) I think the problem come from the nested SQL queries. (sorry for my english, i'm french student)

SELECT DISTINCT `gst.codeAP21`, `gst.email`, `gst.date`, `go.amount` 
FROM globe_statistique 
JOIN globe_customers ON `gst.codeAP21`=`gc.codeAP21` 
JOIN globe_orders ON `gc.ID`=`go.FK_ID_customers` 
WHERE `gst.page` = 'send_order' 
AND `gst.date` = FROM_UNIXTIME(`go.date`,'%%Y-%%m-%%d') 

UNION 

SELECT DISTINCT `gst.codeAP21`, `gst.email`, `gst.date`, '-' 
FROM globe_statistique 
WHERE `gst.page` NOT LIKE 'send_order' "
AND (`gst.codeAP21`,`gst.date`) NOT IN 
            ( SELECT `gst.codeAP21`,`gst.date` FROM globe_statistique 
              WHERE `gst.page`='send_order');

Thanks

Re-write NOT IN as LEFT JOIN. And test switch from NOT LIKE to !=. — jarlh, Apr 24 '17 at 09:50
I try to change NOT LIKE to != but it's the same, but i remove the nested query and it work, so the probleme come from the rested query ... My table globe_statistique : 56000 rows — Gabi, Apr 24 '17 at 09:53

score 0 · Answer 1 · edited Apr 24 '17 at 09:56

0

try this:

SELECT DISTINCT `gst.codeAP21`, `gst.email`, `gst.date`, `go.amount` 
FROM globe_statistique 
JOIN globe_customers ON `gst.codeAP21`=`gc.codeAP21` 
JOIN globe_orders ON `gc.ID`=`go.FK_ID_customers` 
WHERE `gst.page` = 'send_order' 
AND `gst.date` = FROM_UNIXTIME(`go.date`,'%%Y-%%m-%%d') 

UNION 
SELECT DISTINCT t1.`gst.codeAP21`, t1.`gst.email`, t1.`gst.date`, '-' 
FROM globe_statistique t1 
left join globe_statistique t2 on t1.gst.page =t2.gst.page and t1.gst.date =t2.gst.date and t2.gst.page =send_order
WHERE `gst.page` <> 'send_order' AND t2.gst.date is null

But i recomment to rename your column names and remove the dots.

Also use EXPLAIN to find out why the query is slow and add the correct index

edited Apr 24 '17 at 09:56

paparazzo

44,497
23
105
176

answered Apr 24 '17 at 09:54

Jens

67,715
15
98
113

HI ! Thanks for the response, but i don't understand the last line, what found `gst.page` <> 'send_order' ? – Gabi Apr 24 '17 at 10:16
@Gabi sould be != – Jens Apr 24 '17 at 10:17

score 0 · Answer 2 · edited May 23 '17 at 12:02

0

try to avoid the use of distinct. To this end, UNION ALL should be used. Group by at the end gives the same result:

select codeAP21, email, date, amount 
from ( --> your query without distinct but with UNION ALL <-- ) 
group by codeAP21, email, date, amount

see: Huge performance difference when using group by vs distinct

edited May 23 '17 at 12:02

Community

1
1

answered Apr 24 '17 at 09:55

frank

103
10

1

There's a `UNION` already, so that will be doing an implicit `DISTINCT` too – Rowland Shaw Apr 24 '17 at 09:56
you are right. you should also do a union all instead. I'll edit my answer – frank Apr 24 '17 at 09:59

sql request too long ... How to simplify?

2 Answers2