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 <http://www.dnadirect.com>
___________________
This e-mail and any attachments may contain CONFIDENTIAL information,
including PROTECTED HEALTH INFORMATION. If you are not the intended
recipient, any use or disclosure of this information is STRICTLY
PROHIBITED; you are requested to delete this e-mail and any attachments,
notify the sender immediately at 877-646-0222 or by email at
[email protected] <mailto:[email protected]
<mailto:[email protected]>
_______________________________________________
ECM mailing list
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm
To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
