I tuned database yesterday following the indications on http://ez.no/community/articles/tuning_mysql_for_ez_publish but that didn't fix the problem.
I think the problem comes from using the operator like with a string with wildcards, i'll check with mysql forums. I'm also looking if it performs better changing the order of the where. Should i post it as a bug? sugestion?
I've added a bug report about this. Please see: http://ez.no/bugs/view/9091 You can try the index I've listed there, and see if they improve the performance.
Indexes really worked. There are two sqls wich throw errors:
mysql> create index ezurlalias_is_wildcard on ezurlalias( is_wildcard );
ERROR 1061 (42000): Duplicate key name 'ezurlalias_is_wildcard'
mysql> create index eznode_assignment_is_main on eznode_assignment( is_main );
ERROR 1061 (42000): Duplicate key name 'eznode_assignment_is_main'
Kore, thanks for posting the new indexes. We applied them as well ;) Just a note: Can you have a look at the ezcontentobject.language_mask comparisons in the WHEREs, so this could be optimized for single lingual sites?