Preformance in multiple join

[Rasmus Toftegaard]

Hi

I am using mySQL 4.1 and I am not super user in all mySQL smart functions.

I try to build up at database containing phonecalls and prices. The tables look like this:
[phonecall]
start_date
dial_string
duration

[prices]
dial_string
price
start_date
end_date
price_type

Each phonenumber can have more prices with different price_type. The price_type field contains "enduser,client_routing,company_routing,client_out,company_out". The start_date and end_date for the prices is the interval where the price is valid.

I have tried to do like this:
SELECT D.start_date,D.duration,D.dial_string, Sum(duration) as TotalTime FROM phonecall D LEFT JOIN
(SELECT * FROM prices WHERE price_type='enduser') P1
ON PC.dial_string=P1.dial_string AND (D.starte_date BETWEEN P1.start_Date AND P1.end_date) LEFT JOIN
(SELECT * FROM prices WHERE price_type='client_routing') P2
ON PC.dial_string=P2.dial_string AND (D.starte_date BETWEEN P2.start_Date AND P2.end_date) LEFT JOIN
(SELECT * FROM prices WHERE price_type='client_routing') P3
ON PC.dial_string=P3.dial_string AND (D.starte_date BETWEEN P3.start_Date AND P3.end_date) LEFT JOIN ....... until P5
WHERE ..... GROUP BY D.start_date

My problem is that this SQL preforme very bad even if I optimize the indexes. How can this be done in a smarter way? I have also tried other ways where i used one single JOIN on the prices(and no where in the prices) table but this gave me different totalTimes because not all calls have all prices. I need all calls with all possible prices, if a price not i precent then null or something else.

Thanks

Rasmus, Denmark