sergiogonzalez/liferay-portal — pull request #190 by matethurzo (2012-03-22 15:45:13+00:00)
Title: LPS-26223

Hi Sergio,

This is a small performance improvement about something the customers always complaining. Well at least one part of it.

When the Liferay database contains huge amount of users the user indexing can be really a pain, it is really slow. We have identified that querying up the users are not really efficient and we can save some time there.

You can find more information in the ticket itself, but in a nutshell the problem is that according to the current logic we are querying the users up based on the companyId. Most of the cases there are 1 companyId in the system, so the companyId column won't really have any kind of deviation making the query running based on this column really inefficient. The database engines usually will read the whole table with every query so when we are querying up a 1000 batch somewhere near the end of the table the query will run for a long time until the DB finds the appropriate records.

The userId on the other hand has a large deviation (since it's unique) making this column perfect for searching the records. If we are querying up the users based on a userId range the DBs will find them very fast since they have to navigate through the B-tree index and is much more faster.

I've performed tests on oracle and on mysql as well and it turned out that the original logic would consume approx. 35000 ms to query up the whole table, however the improved query will consume only 4400 ms to do the same.

Can you please review these changes? On code level it's really simple - some more finders and custom queries and that's all.

Please let me know if you have any questions.

Thanks,

Máté

(cc-ing @zsoltbalogh since he is interested in this fix, because it can help the support.)