Andris Spruds wrote:
>
>
> > Next step, trying to get rid of the
> > correlation:
> >
> > SELECT l.*, m.measurement, m.date_taken
> > FROM dbc_measurements m, dbc_lakes l
> > WHERE (m.date_taken, m.lake_id) =
> > (SELECT MAX(date_taken), lake_id
> > FROM dbc_measurements m2
> > WHERE m2.param_id=41
> > GROUP BY lake_id)
> > AND m.param_id=41
> > AND m.lake_id=l.id
> > ORDER BY m.measurement DESC
>
>
> This query now comlains that "More than one result not allowed".
Oops, fill in an ANY, please
> > dbc_lakes.id is the primary key, I assume?
> Yes.
>
> > Do you have an index for dbc_measurements.param_id?
> Yes. Also on dbc_measurements.lake_id.
>
> > That could help to increase the performance of both queries.
> > You should try EXPLAIN for the subquery by its own, for the
> outer query
> > by its own (except the subquery-predicate) and the whole one again.
> It works for each of the queries, but never on the whole
> query. Maybe I can
> somehow use the fact that I only need the last 20 records from the
> resultset?
No.
But: you use order by DESC and then want to see the LAST results?
ORDER BY .. ASCII and looking for the FIRST ones (stopping the loop
in which the resultrows are handled/fetched/got) sounds more easily.
Elke
SAP Labs Berlin
>
> > Good luck
> > Elke
> > SAP Labs Berlin
>
> Thanks,
> Andris Spruds
>
>
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
