Hi, The JOIN hierarchy ON (hierarchy.id=_nxhier.id OR hierarchy.id = proxies.targetid) is emitted in the query because the query searches both proxies and non-proxy documents. If you want to search only the non-proxies (which could be the case if you don't use proxies at all), then you can add a "AND ecm:isProxy = 0" clause to your original NXQL query. This would make them execute much faster.
In the next Nuxeo version we'll provide a flag so that this can be done automatically for all queries. Note that this join is hard to optimize for some databases because it's not an equi-join, it's a join on a complex condition which optimizers have a hard time with. But it's really needed for Nuxeo's proxy model. Florent On Tue, Sep 15, 2009 at 11:45 PM, <[email protected]> wrote: > Hello All, > > We’re finding that quick search results may take more than 5 minutes to > appear. We’ve tried search using the search field in the top right corner > and also in the relations tab when trying to create a reference between two > documents. > > > > While troubleshooting, we see that Nuxeo is sending this query to our mysql > database: > > > > SELECT DISTINCT `_nxhier`.`id` from `hierarchy` `_nxhier` > > LEFT JOIN `proxies` ON `proxies`.`id` = `_nxhier`.`id` > > JOIN `hierarchy` ON (`hierarchy`.`id`=`_nxhier`.`id` OR > `hierarchy`.`id` = `proxies`.`targetid`) > > LEFT JOIN `versions` ON `_nxhier`.`id` = `versions`.`id` > > LEFT JOIN `fulltext` ON `hierarchy`.`id` = `fulltext`.`id` > > WHERE `hierarchy`.`primarytype` > > IN ('MailMessage', 'Thread', 'Note', 'AdvancedSearch', > 'search_results', 'Document', 'Picture', 'QueryNav', 'Research', 'Article', > 'File', 'ContextualLink', 'Questionnaire') > > AND (MATCH (`fulltext`.`simpletext`, > `fulltext`.`binarytext`) AGAINST ('NNN')) > > AND (`versions`.`id` IS NULL) > > AND (`_nxhier`.`id` <> > 'd0d320e3-6407-4b08-8afd-fdb1718b1345') > > AND NX_ACCESS_ALLOWED(`_nxhier`.`id`, > 'administrators|Administrator|Everyone', > 'Browse|Read|ReadProperties|ReadRemove|ReadWrite|Everything') > > > > > > The ‘NNN’ is the search text. When looking at this query in the mysql query > analyzer, it appears the OR condition for the hierarchy table join causes a > table scan. > > > > JOIN `hierarchy` ON (`hierarchy`.`id`=`_nxhier`.`id` OR `hierarchy`.`id` = > `proxies`.`targetid`) > > > > Explain Plan: (see 4th row down) > > +----+-------------+-----------+--------+----------------------------------+---------+---------+--------------------+------+------------------------------------------------+ > > | id | select_type | table | type | possible_keys | > key | key_len | ref | rows | > Extra | > > +----+-------------+-----------+--------+----------------------------------+---------+---------+--------------------+------+------------------------------------------------+ > > | 1 | SIMPLE | _nxhier | range | PRIMARY | > PRIMARY | 38 | NULL | 6434 | Using where; Using index; > Using temporary | > > | 1 | SIMPLE | proxies | eq_ref | PRIMARY,proxies_id_hierarchy_fk | > PRIMARY | 38 | nuxeo._nxhier.id | 1 | Distinct > | > > | 1 | SIMPLE | versions | eq_ref | PRIMARY,versions_id_hierarchy_fk | > PRIMARY | 38 | nuxeo._nxhier.id | 1 | Using where; Using index; > Not exists; Distinct | > > | 1 | SIMPLE | hierarchy | ALL | PRIMARY | > NULL | NULL | NULL | 7568 | Using where; > Distinct | > > | 1 | SIMPLE | fulltext | eq_ref | PRIMARY,fulltext_id_hierarchy_fk | > PRIMARY | 38 | nuxeo.hierarchy.id | 1 | Using where; > Distinct | > > +----+-------------+-----------+--------+----------------------------------+---------+---------+--------------------+------+------------------------------------------------+ > > 5 rows in set (0.39 sec) > > > > If one side of the OR condition is removed, the table scan is gone and the > query is quite performant. I’ve tested this with Nuxeo 5.2.0 and 5.2.1 … the > manufactured queries are the same. All the columns used in this join are > indexed. We are using mysql version 5.0.51a. > > > > I understand that mysql is not returning the results to Nuxeo in a timely > manner. My question to the group is this: > > > > 1) Has anyone been successful in tuning their mysql instance to help > search performance? > > 2) Has anyone modified Nuxeo to produce a more mysql-friendly query? > > > > ___________________ > > Greg Jenkins > Senior Software Developer > [email protected] > > DNAdirect > Pier 9 Suite 105 > San Francisco, CA 94111 > > P 415-347-3456 > F 415-646-0224 > > www.dnadirect.com > ___________________ -- Florent Guillaume, Head of R&D, Nuxeo Open Source, Java EE based, Enterprise Content Management (ECM) http://www.nuxeo.com http://www.nuxeo.org +33 1 40 33 79 87 _______________________________________________ ECM mailing list [email protected] http://lists.nuxeo.com/mailman/listinfo/ecm To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
