Re: Re: [sqlite] WHERE clause syntax error

[Miha Vrhovnik]

>Something like:
>
>SELECT A.ID
>FROM A
>LEFT JOIN B
>ON A.ID = B.ID
>WHERE B.ID IS NULL
>
>Might be more efficient?
Nope. I rewrote such query to SELECT ID FROM A WHERE ID NOT IN (SELECT ID FROM 
B);

SELECT COUNT(*) AS unread FROM mboxes WHERE
 idMbox = %d AND idMsg IN (SELECT idMsg FROM
 msgStatus WHERE idStatus = %d);

Is almost instant
Where as
SELECT COUNT(*) AS unread FROM msgStatus AS b
  JOIN mboxes AS a ON b.idMsg = a.idMsg WHERE a.idMbox = %d
  AND b.idStatus = %d;

Would take about 9 seconds.
mboxes has 18.000 rows, msgStatus 23.000
So if one would like to argue, I can provide additional details.

Regards,
Miha