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 <
>[email protected]> 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
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
>--
>D. Richard Hipp
>[email protected]
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
