Hello,
I've debugged indexing speed of JA Megafilter, and fixed a code portion that results into a massive performance improvement, 90x times faster when indexing.
The 90x times is not an exaggeration, it's true, for example indexing a megafilter that took 95 seconds to index, it now takes 1.5 seconds to index.
The fix is in file plugins/jamegafilter/content/helper.php, function getCustomFieldsInfo:
Change
->where('c.id = '. (int) $itemId);
To:
->where('fv.item_id = '. $this->_db->quote($itemId));
The reason is that item_id is a VARCHAR column in the fields_values table, not an INT column (because Joomla decided this way). So we need to use 'quotes' around the item_id WHERE, otherwise the index is not used by the query.
(see: https://shatteredsilicon.net/blog/2021/07/11/mariadb-mysql-performance-tuning-optimization-data-types/ )
So with the previous code, the query index on item_id was not getting used, resulting in each query taking up to 0,500 ms.
Now after this fix, the query uses the index on item_id, and each query is superfast (microseconds).
Hope this helps!