panam <panam(at)gmx(dot)net> wrote:
> I cannot use it because of the way that query is generated
> (by hibernate).
>
> The (simplyfied) base query is just
>
> SELECT b.id from box
>
> the subquery
>
> (SELECT m1.id FROM message m1
> LEFT JOIN message m2
> ON (m1.box_id = m2.box_id AND m1.id < m2.id )
> WHERE m2.id IS NULL AND m1.box_id = b.id) as lastMessageId
>
> is due to a hibernate formula (containing more or less plain SQL)
> to determine the last message id for that box. It ought to return
> just one row, not multiple. So I am constrained to the subquery in
> all optimization attemps (I cannot combine them as you did), at
> least I do not see how. If you have an idea for a more performant
> subquery though, let me know, as this can easily be replaced.
Maybe:
(SELECT max(m1.id) FROM message m1 WHERE m1.box_id = b.id)
-Kevin