Hi,

I have a relatively complex query for the following situation. There are trucks with coupling(s) join them (1 or more). There are routes(transports) with one or more fuel bills on a route. The db schema simplified looks the following:

Routes:
-> id
-> start_date
-> arrival_date
-> start_km
-> end_km

Couplings:
-> id
-> plate_number

Routes_Couplings: (Routes,Couplings join table, n:m relation):
-> route_id
-> coupling_id

Fuels:
-> id
-> buy_date
-> amount

Routes_Fuels: (Routes,Fuels join table, n:m relations):
-> route_id
-> fuel_id

I had to make a select with the following columns whit in:
-> Couplings.plate_number (grouped by)
-> All taken fuels SUM(Fuels.amount)
-> The routes distance (Routes.end_km - Routes.start_km)

I came to the following result:
Is there a more optimized way? I know this is not ANSI SQL compatible, would prefer that as well.

thanks
Sandor

Firstly, are you storing the start_date as text? Is there an index on this field? If there is I am not sure whether this will be picked up with this WHERE clause. If you do an explain on this select statement we can verify this. If it does not pick it up then use r.start_date >= '2011-01-01' and r.start_date < '2011-02' which should use the index. Also adding in the GROUP BY cp.plate_number has no impact and can be removed.

thanks for the hints,

my question regarding "more optimized way" was if it can be overwritten to support ANSI SQL for a potential db migration? I was using mysql until now and want to extend my knowledge regarding ANSI SQL.

I didn't wrote an exact schema yesterday just because my statement above.
Of course as I wrote it here (mysql section) because querying datetime columns this way in mysql is ok (based on my experience) the ANSI way would be using year and month functions (I think both very common across db platforms, even not ,I suppose, ANSI supported functions).

Optimizing queries through good indexes is a must, which I know so my questions are more theoretical.

Right now I got a new theoretical version of the schema which is the following:
(the n:m relations is between Routes and Fuels has been changed to 1:n relations, 1 Route contain 0 or more fuels taken)

Routes:
-> id
-> start_date
-> arrival_date
-> start_km
-> end_km

Couplings:
-> id
-> plate_number

Routes_Couplings: (Routes,Couplings join table, n:m relation):
-> route_id
-> coupling_id

Fuels: (1:n relation to Routes)
-> id
-> route_id
-> buy_date
-> amount
And my current theoretical query look like this:
I know this is still wrong as of a coupling can be part of more than one routes and a route can contain more couplings as well. But I keep working on it.

thanks
Sandor

sorry for my english if I was misleading in any way

to make this ANSI compatible, and give the optimizer a chance to use an index on the start_date column (which it cannot do if you apply YEAR() and MONTH() functions), write it this way --