Re: Optimize query for listing un-read messages

Yes, I was a bit too fast. but replace it with

WHERE NOT prop.is_read = TRUE

and it should be OK.

2014-05-04 18:40 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:

> På søndag 04. mai 2014 kl. 14:06:35, skrev Brice André <
> brice(at)famille-andre(dot)be>:
>
> Dear Andreas,
>
> For me, putting both "LEFT OUTER JOIN" and "NOT EXISTS" is a bad idea.
>
> As the "LEFT OUTER JOIN" will put fields of non-existing right table to
> null, I would simply rewrite it :
> SELECT ... FROM message m
> LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND
> prop.person_id = 1
> WHERE prop.is_read = TRUE
>
> I would also ensure that an efficient index is used for the outer join. I
> would probably try at least a multi-column index on (message_id, person_id)
> for the property table. I would also maybe give a try to an index on
> (message_id, person_id, is_read), just to see if it improves performances.
>
>
> The problem is that your suggested query doesn't return the desired
> results as it effectively is an INNER JOIN because you have "WHERE
> prop.is_read=TRUE", defeating the whole purpose of a LEFT OUTER JOIN.
>
> --
> *Andreas Jospeh Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com
> www.visena.com
> <https://www.visena.com>
>
>