Andris Spruds wrote:
>
> Dear MaxDB list,
>
> The query
> "EXPLAIN SELECT l.*, m.measurement, m.date_taken
> FROM dbc_measurements m, dbc_lakes l
> WHERE m.date_taken = (SELECT MAX(date_taken) FROM
> dbc_measurements m2 WHERE
> m2.lake_id = m.lake_id AND m2.param_id=m.param_id)
> AND m.param_id=41
> AND m.lake_id=l.id
> ORDER BY m.measurement DESC"
>
> returns
> [-9205]: System error: AK Catalog information not
> found:FF000019023F02200081000000000000000000000000000000000000
> 00000000000000
> 00000000000
>
Urgh, we will check why this is returned.
But to increase performance:
now you use a so-called correlated subquery, where some value from
the outer query is used in the inner one. This is usually a hard job
for the database system. You can help the server:
* you know that you are looking for m.param_id = 41 (or usually
a variable with one value). Then tell the inner query, too, that you are
searching for exactly this value. -->
SELECT l.*, m.measurement, m.date_taken
FROM dbc_measurements m, dbc_lakes l
WHERE m.date_taken = (SELECT MAX(date_taken) FROM
dbc_measurements m2 WHERE
m2.lake_id = m.lake_id AND m2.param_id=41)
AND m.param_id=41
AND m.lake_id=l.id
ORDER BY m.measurement DESC
better, but not good. 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
dbc_lakes.id is the primary key, I assume?
Do you have an index for dbc_measurements.param_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.
Good luck
Elke
SAP Labs Berlin
> The problem is that the query itself takes at least 5 seconds
> to execute,
> and I would like to optimize the tables so that it runs
> faster. But the
> EXPLAIN statement dies with an error. Am I doing something
> wrong? The MaxDB
> manual does not contain any useful information on the
> beforementioned error
> message.
>
> With best regards,
> Andris Spruds
>
>
>
>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
