0

I am trying to figure out why the query needs so long, so I can optimize it.

I tried it with EXPLAIN:

EXPLAIN SELECT * FROM (
SELECT p.*, ol. prod_id olpid
FROM products p LEFT JOIN orderlines ol
ON p. prod_id = ol. prod_id ) pol
WHERE pol. olpid IS NULL
ORDER BY category , prod_id;

When I run this on my Workbench, I get the Error Code for Connection lost after 10min (600sec). After I set an Index in Prod_ID, the query answers in roughly a second (perfect), before that, it was nearly impossible to get an answer. Also EXPLAIN PLAN could execute it in a few seconds. Still I want to use EXPLAIN on the Query before using an Index.

Any idea here?

user998692
  • 5,172
  • 7
  • 40
  • 63
JOP
  • 25
  • 1
  • 4
  • 9

1 Answers1

1

You don't need a subquery here. Use this query instead:

SELECT p.*, ol.prod_id olpid
FROM products p LEFT JOIN orderlines ol
ON p.prod_id = ol.prod_id
WHERE ol.prod_id IS NULL
ORDER BY category, prod_id;

Your initial query takes a longer time to execute because you are using a subquery. The subquery needs to be executed entirely, then the WHERE condition may be applied, and at the end the records are ordered. This is also the reason why your query with EXPLAIN is slow.

Jocelyn
  • 11,209
  • 10
  • 43
  • 60
  • hm i tried it, no success: Error Code: 1054. Unknown column 'olpid' in 'where clause' Even if i don't need the subqery, i want to use it. I read that EXPLAIN PLAN don't run over the hole Data set. So why can't it answer in a few secs? Hope you know what i mean – JOP Mar 24 '13 at 18:06
  • Thanks, but still an ERROR: Error Code: 1052. Column 'prod_id' in where clause is ambiguous. Anyway, thanks for your help, unfortunately my main is question is how i can run the query with EXPLAIN(my mainquery). Shouldn't the answer be there in a few secs? Or am i missign sth.(the query should stay 1:1) thx – JOP Mar 24 '13 at 19:30
  • I updated the query again. Read the explanation in my answer regarding the performance of EXPLAIN. – Jocelyn Mar 24 '13 at 19:32
  • It seems to me there are some extra spaces in the ON clause; I think it should be `ON p.prod_id = ol.prod_id` – grahamj42 Mar 24 '13 at 20:48
  • No it actually works perfectly. Thanks a lot. And even more for the explanation of my actual query. That was the important part. :) – JOP Mar 24 '13 at 22:18
  • @grahamj42 I removed the extra spaces. – Jocelyn Mar 24 '13 at 23:22