To admins: JA Megafilter fix for massive performance improvement (90x speed)

ja-developer

thesdhotel Hi
Thank you for posting I'll pass your comment to our dev team.

thesdhotel

ja-developer No problem! I look forward to their opinion 🙂

ja-developer

thesdhotel We already added to our dev list

thesdhotel

Hello,
To the Megafilter staff.
@vangogh and the rest of the staff.

This fix is still not implemented in the official JA Megafilter version.

The official JA Megafilter version still suffers from very long indexing speeds, this fix described here can reduce indexing speed from 100 seconds to 1 second.

I suggest again to implement it in the official version.

manhta

thesdhotel
Hi,
We've added your fix to megafilter. Could you help me make a performance test?
Thanks.

helper.zip

7kB

manhta

thesdhotel ("quote" instead of "q", not sure why you just put "q")

Hi,
That is a alias of quote function, you can see it on these files:

libraries\vendor\joomla\database\src\DatabaseQuery.php (joomla 4) line 1535
libraries\joomla\database\query.php (joomla 3) line 17

That syntax works on both j4 and j3, not sure why it does not work at your side.
Could you check again?
Regards

thesdhotel

Hello, sorry, yes, I re-tested and I confirm it works even with just ->q instead of ->quote.

Although not sure why you decided to use ->q instead of ->quote, as other queries in the file already use ->quote, but up to you 🙂

So yes, I confirm the fix works 🙂

By looking at the query I can spot another quick fix:

This line can be removed:
->join('INNER', '#__content as c ON fv.item_id = c.id')
As it's not used by the query anymore, joining the content table is not needed.

manhta

thesdhotel As it's not used by the query anymore, joining the content table is not needed.

Hi,
We're joining content table to make sure that those field values must come from "real article". This could potentially increase the workload on MySQL, but I believe it's necessary.
Do you have any alternative suggestions?

thesdhotel

Joining on the content table does not help to make sure it's a real article. It just checks if the ID exists, which is unnecessary, as the ID will exist in 99% of cases, and doesn't guarantee that field is not actually used by eg. a com_contact with the same ID.

A better approach could be to instead check the context of the field in the fields table, so replace this:
->join('INNER', '#__content as c ON fv.item_id = c.id')
with this:
->where('f.context = ' . $this->_db->quote('com_content.article') )

This will ensure it only gets fields assigned to real articles (and not other components such as com_contact or or com_users), without needing an extra join 🙂

manhta

thesdhotel
Great,
I've added your fix to this file.
Kindly check.
Thanks

helper.zip

7kB

thesdhotel

It works! Much faster indexing speed now, glad you implemented this 🙂

manhta

thesdhotel
Hi,
These changes will be included in upcoming release.
Thank you for your contribution.