Feature #5357

Optimizing queries on c_document + c_item_property

Added by Yannick Warnier almost 7 years ago. Updated over 6 years ago.

Needs testing
Target version:
Start date:
Due date:
% Done:


Estimated time:
Spent time:
SCRUM pts - complexity:


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            
                   = 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            
                   = 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
                   = 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

Associated revisions

Revision d0a4d90a (diff)
Added by Julio Montoya almost 7 years ago

Fixing query in order to use indexes more efficiently see #5357

Revision 1c8ec45f (diff)
Added by Julio Montoya over 6 years ago

Adding idx_itemprop_id_tool index see #5357



Updated by Julio Montoya almost 7 years ago

  • % Done changed from 20 to 50

query was changed


Updated by Julio Montoya almost 7 years ago

  • Target version changed from 1.9.2 to 2.0

Updated by Julio Montoya over 6 years ago

  • Status changed from Assigned to Needs testing
  • Assignee deleted (Julio Montoya)
  • % Done changed from 50 to 80

index was added

Also available in: Atom PDF