Optimizing queries on c_document + c_item_property
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