Hi Alexey,

1. It produced another bug: https://liferay.atlassian.net/browse/LPS-31539#icft=LPS-31539 - it will not work in case we have subcategories.

Fortunately it's already fixed, thus I've marked https://liferay.atlassian.net/browse/LPS-31539#icft=LPS-31539 as a duplicate of https://liferay.atlassian.net/browse/LPS-29210#icft=LPS-29210.

2. Could you please elaborate more on the phenomenon you're experiencing?

In fact https://liferay.atlassian.net/browse/LPS-28364#icft=LPS-28364 has been created over the course of investigating a customer issue and I've measured significant performance improvement (asset publisher became ~8x faster). Having poor DB performance can be caused by various factors (eg. inefficient query plan due to stale statistics, mis-configured DB instance, on-disk sorts, improperly design I/O subsystem, etc.) and not only from bad SQL. So having this in mind please check the execution plain of those queries (you can also profile them) and it's worth taking a look at your my.cnf. Thereafter if it's still a blocking issue for you, please create and new LPS and provide those technical details by the means of which we can start to reproduce.

Thanks,
Laszlo.

I have two issues with this bug-fix:
1. It produced another bug: https://liferay.atlassian.net/browse/LPS-31539#icft=LPS-31539 - it will not work in case we have subcategories
2. In my case it is not improved perfomance. I have about 25000 AssetEntries, most of them assigned to one category. Then I'm trying to display them (for example first 20) in asset publisher perfomance still ugly. I'му tested on Liferay 6.1.0 (plus backported fix) on MySQL 5.5. At least not at level I've expected - sql query generataed by Asset Publisher still bottleneck

PASSED Manual Testing using the following steps:

1. Add 7 categories (test1, test2, test3, test4, test5, test6, test7).

2. Add 6000 Bookmarks.

   • 1~1000 bookmarks add categories(test1, test2, test3)

   • 1001~2000 bookmarks add categories(test1, test4)

   • 2001~4000 bookmarks add categories(test5, test6)

   • 4001~5000 bookmarks add categories(test1, test7)

   • 5001~6000 bookmarks add categories(test1, test4, test7)

   • 6001~7000 bookmarks add categories(test1, test2, test3, test4, test5, test6, test7)

3. Add 4 pages (page1, page2, page3, page4).

4. every pages add Asset Publisher portlet.

5. Go to configuration in Asset Publisher portlet.

6. select categories in Filter.

   • page1: select "Contains", "Any" and "categories" "test1, test4, test7"

   • page2: select "Does not Contain", "Any" and "categories" "test3, test4, test6, test7"

   • page3: select "Contains", "All" and "categories" "test1, test2, test3"

   • page4: select "Does not Contain", "All" and "categories" "test1, test2, test3, test4, test5, test6, test7"

7. Restart server.

8. Login liferay.

9. Click page1. (Click on the 2000 times. But please count the times for the last 1000 clicks on page1)

   • example: Selenium or QTP. (But remember that SQL query time)

10. Restart server.

11. Login liferay.

12. Click page2. (Click on the 2000 times. But please count the times for the last 1000 clicks on page1).

    • example: Selenium or QTP. (But remember that SQL query time)

13. Restart server.

14. Login liferay.

15. Click page3. (Click on the 2000 times. But please count the times for the last 1000 clicks on page1).

    • example: Selenium or QTP. (But remember that SQL query time)

16. Restart server.

17. Login liferay.

18. Click page4. (Click on the 2000 times. But please count the times for the last 1000 clicks on page1).

    • example: Selenium or QTP. (But remember that SQL query time)

Reproduced on:

Tomcat 7.0 + MySQL 5. Portal 6.2.x GIT ID: 774f50a460e306da3634790dced3e43ba0884205.

SQL query time a little long.

Fixed on:
Tomcat 7.0 + MySQL 5. Portal 6.2.x GIT ID: 42351a8c7e93db896d004dfa273b70168d411d26.
Tomcat 7.0 + MySQL 5. Portal 6.1.x EE GIT ID: 32d80b3096d5dddd356cf616f8ff0972bceb88ad.
Tomcat 7.0 + MySQL 5. Portal 6.1.20 EE GIT ID: b63a515afd2116f7d0e5f9ef2e71b34e5538f1cf.

SQL query time speed faster than influence version.

Committed on:
Portal 6.1.x CE GIT ID: 1b012819651e2a40001449136e12118cdc62c08e.
Portal 6.2.x GIT ID: 5467b6d51dbc45611dc8c9156551f0ecb78de244.

Compatibility with various databases has been tested successfully so far on MySQL, Oracle 11g, DB2 9.7, SQL Server 2008R2, PostgreSQL.