Alfresco lucene query performance

[aaditvmajmudar]

Hi All,

We are having document listing page which fetches the documents with page size of 25/100/500. (Total around 32K documents)

This takes long time to fetch the documents and we are trying to optimize the mysql.

We have only one custom table which is used to store and fetch the count.

We got the below log from mysql.

- Slow Queries: 1.25
- Number of Joins that need an index in the last 24hours:1476.1370

Is this looks Ok OR we need to do anything with this?

How can we improve performance and decrease the time?

[kaynezhang]

In embedded Lucene permissions are checked after lucene returned all results,That means if you want to search with page size 500,luene will search more that 500(default is 1000) doucuments and starts to check the permissions one by one. So the larger the page size you set ,the slower it will get.
You should consider upgrading to Alfresco 4 and use Solr,in solr permissions are checked at query time ,it has better performance.

[aaditvmajmudar]

Thanks for your answer.

At this stage, we have identified that the sorting on "modified date" takes around "15-20sec" for around 30K contents.

Other than "modified date" property, all other properties like name, created date and custom  properties takes around "4 sec" for sorting.

What can be the root cause for this issue that only "modified date" sorting takes much long time to return the results?

How to resolve this problem?

[kaynezhang]

Have you ever configured "modified date" to use a different lucene analyzer.as I remember by default "created date" and "modified date" are configured to use same DateAnalyer in alfresco 3.4,so sorting by them should take nearly same time.

[aaditvmajmudar]

No, we have not configured any different lucene analyzer.

But sorting with created date is not taking much time as modified date.

Sorting with created date takes around 4-5 sec only.