See http://git.gnome.org/browse/anjuta/tree/plugins/symbol-db/symbol-db-model-project.c and http://git.gnome.org/browse/anjuta/tree/plugins/symbol-db/symbol-db-model-file.c . Optimize the following sql statement used in there:

Global view:

#define SDB_MODEL_PROJECT_SQL " \
    SELECT \
        symbol.symbol_id, \
        symbol.name, \
        symbol.file_position, \
        symbol.scope_definition_id, \
        symbol.signature, \
        symbol.returntype, \
        file.file_path, \
        sym_access.access_name, \
        sym_type.type_type, \
        sym_type.type_name, \
        (symbol.kind_id IN \
        ( \
            SELECT sym_kind_id \
            FROM sym_kind \
            WHERE kind_name IN ('class', 'namespace', 'enum', 'struct', 'union') \
        )) AS has_child \
    FROM symbol \
    LEFT JOIN file ON symbol.file_defined_id = file.file_id \
    LEFT JOIN sym_access ON symbol.access_kind_id = sym_access.access_kind_id \
    LEFT JOIN sym_type ON symbol.type_id = sym_type.type_id \
    WHERE \
    ( \
        symbol.scope_id = ## /* name:'parent' type:gint */ \
        AND symbol.kind_id IN \
        ( \
            SELECT sym_kind_id \
            FROM sym_kind \
            WHERE \
            ( \
                0 = ## /* name:'parent' type:gint */ \
                AND sym_kind.kind_name IN ('class', 'struct', 'union', 'enum') \
            ) \
            OR \
            ( \
                0 != ## /* name:'parent' type:gint */ \
                AND sym_kind.kind_name != 'namespace' \
            ) \
        ) \
    ) \
    OR \
    ( \
        symbol.symbol_id IN \
        ( \
            SELECT symbol_id \
            FROM symbol \
            WHERE \
                symbol.scope_id = ## /* name:'parent' type:gint */ \
                AND symbol.kind_id IN \
                ( \
                    SELECT sym_kind_id \
                    FROM sym_kind \
                    WHERE sym_kind.kind_name = 'namespace' \
                ) \
            GROUP BY symbol.scope_definition_id \
            \
        ) \
    ) \
    ORDER BY symbol.name \
    LIMIT ## /* name:'limit' type:gint */ \
    OFFSET ## /* name:'offset' type:gint */ \
    "


Local view:

#define SDB_MODEL_FILE_SQL " \
    SELECT \
        symbol.symbol_id, \
        symbol.name, \
        symbol.file_position, \
        symbol.scope_definition_id, \
        symbol.signature, \
        symbol.returntype, \
        file.file_path, \
        sym_access.access_name, \
        sym_type.type_type, \
        sym_type.type_name, \
        (symbol.kind_id IN \
        ( \
            SELECT sym_kind_id \
            FROM sym_kind \
            WHERE kind_name IN ('class', 'namespace', 'enum', 'struct', 'union') \
        )) AS has_child \
    FROM symbol \
    LEFT JOIN file ON symbol.file_defined_id = file.file_id \
    LEFT JOIN sym_access ON symbol.access_kind_id = sym_access.access_kind_id \
    LEFT JOIN sym_type ON symbol.type_id = sym_type.type_id \
    WHERE \
    ( \
        file.file_path = ## /* name:'filepath' type:gchararray */ \
        AND symbol.scope_id = ## /* name:'parent' type:gint */ \
        AND symbol.kind_id NOT IN \
        ( \
            SELECT sym_kind_id \
            FROM sym_kind \
            WHERE sym_kind.kind_name = 'namespace' \
        ) \
    ) \
    OR \
    ( \
        symbol.symbol_id IN \
        ( \
            SELECT symbol_id \
            FROM symbol \
            LEFT JOIN file ON symbol.file_defined_id = file.file_id \
            WHERE \
                file.file_path = ## /* name:'filepath' type:gchararray */ \
                AND symbol.scope_id = ## /* name:'parent' type:gint */ \
                AND symbol.kind_id IN \
                ( \
                    SELECT sym_kind_id \
                    FROM sym_kind \
                    WHERE sym_kind.kind_name = 'namespace' \
                ) \
            GROUP BY symbol.scope_definition_id \
            \
        ) \
    ) \
    OR \
    ( \
        symbol.scope_id = ## /* name:'parent' type:gint */ \
        AND symbol.kind_id NOT IN \
        ( \
            SELECT sym_kind_id \
            FROM sym_kind \
            WHERE sym_kind.kind_name = 'namespace' \
        ) \
        AND symbol.scope_definition_id IN \
        ( \
            SELECT scope_id \
            FROM symbol \
            JOIN file ON symbol.file_defined_id = file.file_id \
            WHERE file.file_path = ## /* name:'filepath' type:gchararray */ \
            GROUP BY symbol.scope_id \
        ) \
    ) \
    ORDER BY symbol.file_position \
    LIMIT ## /* name:'limit' type:gint */ \
    OFFSET ## /* name:'offset' type:gint */ \
    "

Following optimizations can be possible:

1) Get rid of namespace hacks. The last OR expressions in both statements, and various select sub-queries involving kind = 'namespace' are the hacks to remove. This depends on bug 615403 .

2) Get rid of first select sub-query by introducing a new column 'has_child' in sym_kind table. This depends on bug 615428 .

3) See if we are missing any index in above WHERE clauses, or if we are utilizing existing indexes optimally.