> I have been analyzing the Query PLAN. In the query "Select * FROM > WordGroupView Where idWordGroup = 1;" the database engine perform a scan > over WordGroup table, and it is not necessary since the where clause is > defined. However, using directly the query of the view change the SCAN by a > SEARCH, so the query is faster answered. > > Please, someone know what is the problem?
Probably SQLite's optimizer is not so smart to figure out that combination of GROUP BY in the inner SELECT and WHERE in the outer one on the same field can result in removing of GROUP BY and adding WHERE to inner query. Or when you say that using select from view directly results in faster query do you use query like the following? Select * FROM ( SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, count(DISTINCT dw.Document_idDocument) AS documentsCount FROM WordGroup wg LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord GROUP BY wg.idWordGroup; ) Where idWordGroup = 1; Pavel On Thu, Dec 1, 2011 at 7:42 PM, Manuel Jesus Cobo Martin <mjc...@gmail.com> wrote: > Hello, > > I am new in this mailing list. > > I am developing a Java tool and I use SQLite as file format. At this > moments, I did not have any problem, and SQLite works quite fine. > > I want to create some views to generate statistical and aggregate data in > order to show more information to the user. > > The follow code is an example of a View: > > CREATE VIEW WordGroupView AS > SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, count(DISTINCT > dw.Document_idDocument) AS documentsCount > FROM WordGroup wg > LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup > LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord > GROUP BY wg.idWordGroup; > > The problem is when I am using the view in other query. > > For example, the query "Select * FROM WordGroupView Where idWordGroup = 1;" > is very slow. Whereas, if I use directly the main query of the view, is > faster (1400ms vs 7ms). > > I think that I am doing something in wrong way but I do not understand what > is happens. > > I have been analyzing the Query PLAN. In the query "Select * FROM > WordGroupView Where idWordGroup = 1;" the database engine perform a scan > over WordGroup table, and it is not necessary since the where clause is > defined. However, using directly the query of the view change the SCAN by a > SEARCH, so the query is faster answered. > > Please, someone know what is the problem? > > Thank you in advance. > > Best Regards, > > Manuel Jesús. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
