"Markus Wolters" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I've got a problem in combining expressions. On combining multiple > ANDs with an OR within a SELECT WHERE clause, SQLite takes years to > respond. (Database has a size of only about 1 MB). > > Using two SELECTs and combining them by UNION works like a charm! > What could be the problem here? > > This statement takes about 10 seconds after result can be read: > > SELECT <.> FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = > s.MovieId AND ((s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND > c.CategorieId IN (60)) OR Rating IN (2))
Categories table is not joined to other tables on one side of OR. As a result, you build a full cross-product with Categories table: every record with (m.Id = s.MovieId AND Rating=2) is duplicated once for each category. > SELECT <.> FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = > s.MovieId AND s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND > c.CategorieId IN (410) > > UNION > > SELECT <.> FROM Scenes AS s, Movies AS m WHERE m.Id = s.MovieId AND > s.Rating IN(1) The second statement in the UNION doesn't mention Categories table, so no cross-product. The two queries are not equivalent. The fact that they return different number of rows should have given you a hint. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
