Please run the ".fullschema" command on your database and send us the output.
I mean by this: (1) Download the latest version of sqlite3.exe from the website. (2) Run "sqlite3 YOURDATABASE .fullschema >out.txt" (3) Include the text of out.txt in the body of a follow-up email. That information will assist us in answering your question. On Thu, Sep 25, 2014 at 6:54 AM, Kraijenbrink - FixHet - Systeembeheer < kraijenbr...@fixhet.nl> wrote: > Hi all, > > I've searched through this forum but couldn't find any related topic > regarding my question. I'm having serious performance problems (queries up > to 20/sec) while running a SQLite query since i added a group_concat clause. > > The query looks like: > > SELECT GROUP_CONCAT(Parent.fkintFolderID,'\') FilePath > FROM tblFolderNestedSets Node > , tblFolderNestedSets Parent > WHERE Node.intLeft BETWEEN Parent.intLeft AND Parent.intRight > AND Parent.fkintSessionID = Node.fkintSessionID > AND Node.fkintSessionID = 1817 > AND Node.fkintFolderID = 1937926; > > Query result: > > 1927916\1934826\1936323\1937926 > > Changing the first line "SELECT GROUP_CONCAT(...." into "SELECT > Parent.fkintFolderID..." increased performance by an order of magnitude. > > explain query plan returns: > > selectid order from detail > 0 0 0 SEARCH TABLE tblFolderNestedSets AS Node > USING COVERING INDEX tblFolderNestedSets_SessionID_FolderID_Right_Left > (fkintSessionID=? AND fkintFolderID=?) (~9 rows) > 0 1 1 SEARCH TABLE tblFolderNestedSets AS > Parent USING COVERING INDEX > tblFolderNestedSets_SessionID_FolderID_Right_Left (fkintSessionID=?) (~5 > rows) > > My question is: how can I improve performance and keep using GROUP_CONCAT > at the same time? > > Thanks in advance. > > Peter > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
