This Query looks much like it is generated by the tag service when searching for documents with a given tag (the code is in class TagQueryUtils, method getDocumentsWithTag, in the xwiki-platform-core/xwiki-platform-tag/xwiki-platform-tag-api module)
This query might be triggered by any kind of UI element (Panel, macro etc.) I do not think it is used to update any search index or the like. Instead it is used on some pages, e.g. Main.Tags when clicking on a tag to see its list of documents. I wonder why this query takes so long. Even a 100K docs should not be that much (I mean, 5 minutes query time, huh?) Is there any chance some binary data of the movie objects or the like ended up in the xwikilistitems table or any other table used in the query? Clemens > Hello, > > As I mentioned, I discovered that the queries that are hogging my DB are > similar to: > '102', 'xwiki', 'localhost:52614', 'xwiki', 'Query', '372', 'Creating sort > index', 'select xwikidocum0_.XWD_FULLNAME as col_0_0_ from xwikidoc > xwikidocum0_ cross join xwikiobjects baseobject1_ cross join xwikilists > dbstringli2_ inner join xwikiproperties dbstringli2_1_ on > dbstringli2_.XWL_ID=dbstringli2_1_.XWP_ID and > dbstringli2_.XWL_NAME=dbstringli2_1_.XWP_NAME inner join xwikilistitems > list3_ on dbstringli2_.XWL_ID=list3_.XWL_ID and > dbstringli2_.XWL_NAME=list3_.XWL_NAME where (xwikidocum0_.XWD_HIDDEN<>1 oy > xwikidocum0_.XWD_HIDDEN is null) and > baseobject1_.XWO_CLASSNAME=\'XWiki.TagClass\' and > baseobject1_.XWO_NAME=xwikidocum0_.XWD_FULLNAME and > baseobject1_.XWO_ID=dbstringli2_.XWL_ID and dbstringli2_.XWL_NAME=\'tags\' > and lower(list3_.XWL_VALUE)=lower(\'shock-rock\') order by > xwikidocum0_.XWD_FULLNAME' > > Anyone knows what is the component that is responsible for this query? for > each new tag, this kind of query is executed to create sort index? > > Thanks > > > 2014-08-23 3:46 GMT-03:00 O.J. Sousa Rodrigues <[email protected]>: > >> Wouldn't this be a perfect case for a NoSQL-DB like Neo4J? >> Am 22.08.2014 23:13 schrieb "Paul Libbrecht" <[email protected]>: >> >>> Danilo, >>> >>> have you checked the MySQL process list? >>> I'd suspect something is hogging. >>> For search, I'd recommend to leverage solr… but with an amount of >>> customizations. There are some hooks in the solr-plugin, I believe. >>> >>> hope it helps. >>> >>> paul >>> >>> >>> On 22 août 2014, at 22:54, Danilo Oliveira <[email protected]> >>> wrote: >>> >>>> Hello Devs, >>>> >>>> I am developing an application based on XWiki that is mapping, >>> connecting, >>>> relating and graphical disposing movie information in order to make >>>> possible to the user explore their trailers. >>>> >>>> At the beginning with a light data set (<5k movies) the application was >>>> running well, but today I started to populate my database (MYSQL) and >> the >>>> application became unusable, the queries is taking more than 5 minutes >> to >>>> complete. Actually, it has more than 15k movies (1 movie = 1 doc) and I >>>> need to upload more 100k. >>>> >>>> I already have checked the cache and performance page but I don't know >> if >>>> they[1][2] solve my problem: >>>> I think that is a architecture challenge. >>>> >>>> My AS IS process is: >>>> -User insert a movie, >>>> -the application search for the movie and their related films based on >>> its >>>> characteristics (a lot of joins and other algorithms) (bottleneck) >>>> -the application returns the results as a map; >>>> >>>> I am wondering if I could use the custom mapping[3] to solve my problem >>> due >>>> the fact that the relationship information for each movie, in this >> first >>>> moment, don't need to change often. Each movie has X movies related, >>> sorted >>>> by similarity. So, I could create some relationship algorithm that will >>> run >>>> scheduled ( 1 time by week) and populate this new table .I am thinking >> to >>>> use dataframe panda of python to talk directlly with mysql and make >> data >>>> analysis, any other suggestion? >>>> >>>> So I would create a custom map to my relationship movie class, run the >>>> algorithm, populate the new table, so my TO BE would be: >>>> >>>> TO BE >>>> -user insert movie info; >>>> -simple select on the customtable "MoviesRelated"; >>>> -the application returns the results; >>>> >>>> I would appreciate some opinion. Thank you very much. >>>> >>>> [1]http://platform.xwiki.org/xwiki/bin/view/AdminGuide/Performances >>>> [2]http://extensions.xwiki.org/xwiki/bin/view/Extension/Cache+Module >>>> [3]http://platform.xwiki.org/xwiki/bin/view/DevGuide/CustomMapping >>>> >>>> Danilo >>>> -- >>>> Danilo Amaral de Oliveira >>>> Engenheiro de Computação >>>> celular (32) 9111 - 6867 >>>> _______________________________________________ >>>> devs mailing list >>>> [email protected] >>>> http://lists.xwiki.org/mailman/listinfo/devs >>> >>> _______________________________________________ >>> devs mailing list >>> [email protected] >>> http://lists.xwiki.org/mailman/listinfo/devs >>> >> _______________________________________________ >> devs mailing list >> [email protected] >> http://lists.xwiki.org/mailman/listinfo/devs >> > > > mit freundlichen Grüßen Clemens Klein-Robbenhaar -- Clemens Klein-Robbenhaar Software Development EsPresto AG Breite Str. 30-31 10178 Berlin/Germany Tel: +49.(0)30.90 226.763 Fax: +49.(0)30.90 226.760 [email protected] HRB 77554 B - Berlin-Charlottenburg Vorstand: Maya Biersack, Peter Biersack Vorsitzender des Aufsichtsrats: Dipl.-Wirtsch.-Ing. Winfried Weber Zertifiziert nach ISO 9001:2008 _______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
