eZ Community » Forums » Developer » Mysql optimization - large table but...
expandshrink

Mysql optimization - large table but not too huge(100000+records)

Mysql optimization - large table but not too huge(100000+records)

Friday 25 December 2015 8:24:38 am

Hi guys

I found there is a slow query which cause 5-10s to get the result, there are 'index' in the tables, and I also tried to set multi-index, but doesn't work.

It is also slow at my local machine, do you have some experience to optimize the query?

 

Thank you. 

SELECT DISTINCT ezcontentobject.contentclass_id, ezcontentobject.current_version, ezcontentobject.id, ezcontentobject.initial_language_id, ezcontentobject.language_mask, ezcontentobject.modified, ezcontentobject.owner_id, ezcontentobject.published, ezcontentobject.remote_id AS object_remote_id, ezcontentobject.section_id, ezcontentobject.status, ezcontentobject_tree.contentobject_is_published, ezcontentobject_tree.contentobject_version, ezcontentobject_tree.depth, ezcontentobject_tree.is_hidden, ezcontentobject_tree.is_invisible, ezcontentobject_tree.main_node_id, ezcontentobject_tree.modified_subnode, ezcontentobject_tree.node_id, ezcontentobject_tree.parent_node_id, ezcontentobject_tree.path_identification_string, ezcontentobject_tree.path_string, ezcontentobject_tree.priority, ezcontentobject_tree.remote_id, ezcontentobject_tree.sort_field, ezcontentobject_tree.sort_order, ezcontentclass.serialized_name_list as class_serialized_name_list, ezcontentclass.identifier as class_identifier, ezcontentclass.is_container as is_container , ezcontentobject_name.name, ezcontentobject_name.real_translation   FROM ezcontentobject_tree INNER JOIN ezcontentobject ON (ezcontentobject_tree.contentobject_id = ezcontentobject.id) INNER JOIN ezcontentclass ON (ezcontentclass.version = 0 AND ezcontentclass.id = ezcontentobject.contentclass_id) INNER JOIN ezcontentobject_name ON (     ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id AND     ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version )     WHERE ezcontentobject_tree.parent_node_id = 52 and         ezcontentobject.contentclass_id  NOT IN  ( 1 ) AND  ( ezcontentobject_name.language_id & ezcontentobject.language_mask > 0 AND     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 1 )   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 2 ) )   <     ( ezcontentobject_name.language_id & 1 )   + ( ( ezcontentobject_name.language_id & 2 ) ) )  AND ezcontentobject_tree.is_invisible = 0  AND ((ezcontentobject.section_id in (1, 2, 3, 7, 8)))   AND  ezcontentobject.language_mask & 3 > 0   ORDER BY ezcontentobject.published DESCLIMIT 80470, 10 

 

No reply yet!

expandshrink

You must be logged in to post messages in this topic!

36 542 Users on board!

Forums menu

Proudly Developed with from