Monday 07 January 2008 5:54:20 am

Still having troubles with this. i will try to explain it better.

Sometimes, when users in admin section search for a long phrase, we have troubles with the next queries. (for example update a content). If we do a show full processlist on our database we can see that a question related to ezsearchengine takes a long time to end. While these query (status = statistics) is executing, next queries shows a "locked" state. the first one seems to block the others. the server is still responding, but if some editors tries to edit content, we have many queries in our processlist with "locked" state. when we reach our max_user_connections, users can't do anything more and see our site.

That's the query who "locks" the other ones

SELECT DISTINCT ezcontentobject.*, ezcontentclass.serialized_name_list as c
lass_serialized_name_list, ezcontentobject_tree.*
                            , ezcontentobject_name.name as name,  ezcontentobjec
t_name.real_translation
                    FROM
                       ezsearch_tmp__0, ezsearch_tmp__1, ezsearch_tmp__2, ezsear
ch_tmp__3, ezsearch_tmp__4, ezsearch_tmp__5, ezsearch_tmp__6, ezsearch_tmp__7, e
zsearch_tmp__8, ezsearch_tmp__9, ezsearch_tmp__10, ezsearch_tmp__11, ezsearch_tm
p__12 ,
                       ezcontentobject,
                       ezcontentclass,
                       ezcontentobject_tree
                       , ezcontentobject_name

                    WHERE
                     ezsearch_tmp__0.contentobject_id=ezsearch_tmp__1.contentobj
ect_id   AND  ezsearch_tmp__0.contentobject_id=ezsearch_tmp__2.contentobject_id
  AND  ezsearch_tmp__0.contentobject_id=ezsearch_tmp__3.contentobject_id   AND
ezsearch_tmp__0.contentobject_id=ezsearch_tmp__4.contentobject_id   AND  ezsearc
h_tmp__0.contentobject_id=ezsearch_tmp__5.contentobject_id   AND  ezsearch_tmp__
0.contentobject_id=ezsearch_tmp__6.contentobject_id   AND  ezsearch_tmp__0.conte
ntobject_id=ezsearch_tmp__7.contentobject_id   AND  ezsearch_tmp__0.contentobjec
t_id=ezsearch_tmp__8.contentobject_id   AND  ezsearch_tmp__0.contentobject_id=ez
search_tmp__9.contentobject_id   AND  ezsearch_tmp__0.contentobject_id=ezsearch_
tmp__10.contentobject_id   AND  ezsearch_tmp__0.contentobject_id=ezsearch_tmp__1
1.contentobject_id   AND  ezsearch_tmp__0.contentobject_id=ezsearch_tmp__12.cont
entobject_id    AND
                    ezcontentobject.id=ezsearch_tmp__0.contentobject_id AND
                    ezcontentobject.contentclass_id = ezcontentclass.id and
                    ezcontentclass.version = '0' and
                    ezcontentobject.id = ezcontentobject_tree.contentobject_id a
nd
                    ezcontentobject_tree.node_id = ezcontentobject_tree.main_nod
e_id
                     and  ezcontentobject_tree.contentobject_id = ezcontentobjec
t_name.contentobject_id and
                                  ezcontentobject_tree.contentobject_version = e
zcontentobject_name.content_version and
 ( ezcontentobject_name.language_id & ezcontentobject.language_mask > 0 AND
     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezc
ontentobject_name.language_id ) ) & 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezc
ontentobject_name.language_id ) ) & 2 ) )
   <
     ( ezcontentobject_name.language_id & 1 )
   + ( ( ezcontentobject_name.language_id & 2 ) )
 )



                    ORDER BY ezcontentobject.modified DESC
LIMIT 0, 10 

If we search for the same phrase without any of the words all works as expected. We also notice that more words on the search phrase makes this statistics time bigger.

Btw, we also noticed that the query i have shown uses 12 tmp tables. if we quote some of the words, we have 24 tables in this query...

i mean, it's not the same if we search for 'in other country' or 'in "other country"'

I have searched for what could be the reason for this long "statistics" state for this process and i found this http://forums.devshed.com/mysql-help-4/a-long-statistics-state-46232.html

So, finally my questions.

a) Is there any way to optimize this query?
b) I know this is not related to ez, but do you know any way to tell mysql to "kill" this process after an amount of seconds?. If we kill this process manually then the locked queries executes and all works as expected.

thank you so much.