search

joomla / joomla-cms

Home of the Joomla! Content Management System
https://www.joomla.org
GNU General Public License v2.0
4.77k stars 3.65k forks source link

Speeding up all back-end models by eliminating joins in _getListCount #42551

Closed mavrosxristoforos closed 10 months ago

mavrosxristoforos commented 10 months ago

Currently, the BaseDatabaseModel takes the list query and clears the desired ordering, limit and offset, to -hopefully- obtain the number of total rows without performing a getNumRows, by replacing the selected columns with COUNT(*). You can see it here:

https://github.com/joomla/joomla-cms/blob/f8cca4479a19e0238686ef18a62de2774d528dd0/libraries/src/MVC/Model/BaseDatabaseModel.php#L193C44-L193C44

I have a use case where this query took 1.5 seconds, because my list query performs a JOIN on three other tables. By overriding the function in my own model and changing the equivalent of line BaseDatabaseModel.php:193 to also clear('join'), I reduced this query to 4.28ms.

This could potentially win a considerable amount of loading time for all back-end models that perform JOINs on the main list query, with a minimum amount of work, that I could obviously PR myself.

My question is this: Is there a catch that I'm not thinking of, where these JOINs would be necessary even if we're not selecting columns from them? Do they alter the amount of total rows? If they do, could we somehow separate them from those that don't?

richard67 commented 10 months ago

Without having checked code yet I would say the joins might be necessary when joined columns are used in the "WHERE" condition, e.g. for applying a filter, so the query just returns the count and not any of the joined columns, but it might use the joined columns in its "WHERE" clause.

Possibly related or similar issue see #42313 .

mavrosxristoforos commented 10 months ago

Oh my, I guess you are right. I suppose it's also required if you perform any join other than LEFT JOIN. However, saving one second of loading time may be worth investigating. What do you think? Would it make sense to check the where clause and remove unused left joins, or is this an over-optimization?

richard67 commented 10 months ago

I don't really know. If it is not too much effort, investigating could make sense, I think. Let's see if there are more opinions.

mavrosxristoforos commented 10 months ago

Unfortunately, identifying whether a JOIN is required is not very easy with all the table aliases etc. Definitely not worth adding code to the core. It's much preferable to override the _getListCount function in extensions.