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
