Hi, Thank you for the fast response. Below this line you'll find sqlite3.exe's output:
CREATE TABLE tblFolderNames ( pkintFolderNameID integer PRIMARY KEY AUTOINCREMENT NOT NULL, txtName text NOT NULL UNIQUE COLLATE nocase ); CREATE TABLE tblFolders ( pkintFolderID integer PRIMARY KEY AUTOINCREMENT NOT NULL, fkintParentID integer NOT NULL, fkintNameID integer NOT NULL, dtmCreationTime datetime NOT NULL ); CREATE TABLE tblFolderNestedSets ( pkintFolderNestedSetID integer PRIMARY KEY AUTOINCREMENT NOT NULL, fkintSessionID integer NOT NULL, fkintFolderID integer NOT NULL, intLeft integer, intRight integer ); CREATE TABLE tblSessions ( pkintSessionID integer PRIMARY KEY AUTOINCREMENT NOT NULL, dtmStartDate datetime NOT NULL, dtmEndDate datetime ); CREATE UNIQUE INDEX tblFolderNames_Name ON tblFolderNames (txtName COLLATE nocase); CREATE UNIQUE INDEX tblFolders_ParentID_NameID ON tblFolders (fkintParentID, fkintNameID); CREATE UNIQUE INDEX tblFileParts_FileID_DataPartID_DataPartPos ON tblFileParts (fkintFileID, fkintDataPartID, intDataPartPos); CREATE UNIQUE INDEX tblFolderNestedSets_SessionID_FolderID_Right_Left ON tblFolderNestedSets (fkintSessionID, fkintFolderID, intRight, intLeft); /* No STAT tables available */ -- Peter Kraijenbrink >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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users