Hi @ all,

I have written a very inefficient query and I'm desperately trying to get the best performance out of it.
(My MySQL skills are not very good, so please be indulgent while reading...)

First of all: I have read-only access to the DB only. So unfortunately, I have no way of creating temporary tables (which I think would be a good solution).

The goal of my query is to list each and every article (table art), while having the sold quantities of the last three years as seperate columns to the right.
All dokument headers are listed in "dok", all line item are listed in "pos".

All of the table names are altered for simplicity, the real tables and fields have really complex names.

My first try was this (three subquerys):

Code:
select art.id,
art.weight,
art.price,
(select sum(pos.qty) from pos inner join dok on pos.dok_id = dok.id where year(dok.date) = 2013 and pos.art_id = art.id),
(select sum(pos.qty) from pos inner join dok on pos.dok_id = dok.id where year(dok.date) = 2014 and pos.art_id = art.id),
(select sum(pos.qty) from pos inner join dok on pos.dok_id = dok.id where year(dok.date) = 2015 and pos.art_id = art.id)

from art;
This was very inefficient.

So I wrote a query giving me the following resultset (where not every article is included, because not all have been sold):

art.id, year, qty

using it as a table to left join it like so:

Code:
select art.id,
art.weight,
art.price,
t1.qty,
t2.qty,
t3.qty

from (art)

left join (select art.id, year(dok.date) as yr, sum(pos.qty) as qty from pos inner join dok on pos.dok_id = dok.id inner join art on pos.art_id = art.id where year(dok.date) in >= 2013 group by art.id, year(dok.date)) t1 on t1.id = art.id
left join (select art.id, year(dok.date) as yr, sum(pos.qty) as qty from pos inner join dok on pos.dok_id = dok.id inner join art on pos.art_id = art.id where year(dok.date) in >= 2013 group by art.id, year(dok.date)) t2 on t2.id = art.id
left join (select art.id, year(dok.date) as yr, sum(pos.qty) as qty from pos inner join dok on pos.dok_id = dok.id inner join art on pos.art_id = art.id where year(dok.date) in >= 2013 group by art.id, year(dok.date)) t3 on t3.id = art.id

where t1.yr= 2013 and t2.yr= 2014 and t3.yr= 2015;
This query only lasted about 50% of the first one. That was better, but not as good as I think it can get using the ideas of some of you.
The performance of using the subquery as a table is better, but it is still doing it three times, I think.

Is there maybe a way to get MySQL to do the subquery only once, using it for all three joins?
Or is there a completely different way of doing this?

I'm looking forward to your ideas.

Greetings from Germany,

Stefan