westsorkin wrote:

> Thanks for the comments!
>
> Of course, I will put FK and I will have a UserID parameter in my
> query. So, now the question is, how slower is the first query (if it
> is), compared to the second one:
>
> select
> Message.id, Message.Subject
> from
> Messages
> inner join UserMessages on (UserMessages.MessageID = Messages.ID)
> where
> (
> (:show_only_unread = 'YES' and UserMessages.IsRead = 'NO') or
> (:show_only_unread = 'NO')
> )
> and
> (
> UserMessages.UserId = :UserID
> )
>

This query will not use an index (ib available) on UserMessages.IsRead

>
> select
> Message.id, Message.Subject
> from
> Messages
> inner join UserMessages on (UserMessages.MessageID = Messages.ID)
> where
> UserMessages.UserId = :UserID and
> UserMessages.IsRead = 'NO'
>

This Query will use an index on UserMessage.IsRead i available.

> When parameter "show_only_unread" is "No", there should not be any
> performance differences, right? But what about when "show_only_unread"
> is "Yes"?
>

The former query will have the same performance for all messages or for
not read messages. The second will perform better (of course, if an
index exists on IsRead and if this Index will filter out a lot of messages).

> WestSorkin
>

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br