> 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". > 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? > 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]
