Chamilo LMS

On one of our Chamilo portals, we can see many queries like the following:

# User@Host: u[u] @ localhost []
# Query_time: 0.002191  Lock_time: 0.000124 Rows_sent: 1  Rows_examined: 1045
SET timestamp=1345920838;
SELECT SUM(table1.size) FROM (
                SELECT size FROM `chamilo_main`.`c_item_property` AS props, `chamilo_main`.`c_document` AS docs
                WHERE     docs.c_id     = 215 AND
                        props.c_id     = 215 AND            
                        docs.id     = props.ref AND 
                        props.tool     = 'document' AND 
                        path LIKE '/DIR_Lecturas/%' AND                     
                        props.visibility = 1 
                         AND  ( id_session = 918 OR id_session = 0)  
                GROUP BY ref
               ) as table1;

This obviously comes from the weight calculation for a specific directory. I've added the following index, but it doesn't help much:

alter table c_document add index idx_cdoc_path (path(28));

The "28" characters length is just enough to cover 9999 sessions with /shared_session_folder_2389/, which is a frequent query on session-enabled portals.

However, the main problem in this query comes from the way it is built. An index on a MySQL table is most efficient when it is built in the same order as the query. Here, we switch from one table to another in the query, which prevents grouping the criterias for the index:

                WHERE     docs.c_id     = 215 AND
                        props.c_id     = 215 AND            
                        docs.id     = props.ref AND 
                        props.tool     = 'document' AND ...

Making it more oriented to a specific table would enable more efficiency from a two-columns index:

                WHERE     docs.c_id     = 215 AND
                        docs.id     = props.ref AND 
                        props.c_id     = 215 AND            
                        props.tool     = 'document' AND 

This would make it possible to add (at least) an efficient index as:

ALTER TABLE c_item_property ADD INDEX idx_itemprop_id_tool (c_id, tool(8));

The length of 8 is because most of the queries of this type are focused on documents, and that any other "tool" would be easily identified by its first 8 letters (but this might need to be properly benchmarked after a while, sometimes one character more or less can massively affect speed.

Database changes (alter add index) should be added to 1.10, obviously, but the query can already be improved in the code for 1.9.2