Thank you iionly.

I'll check the use of cache.

However, in this specific case the underlying data can change many times per second (depending on the number of users). Therefore, the standard approach of paging may not work here. In addition, the queries depend on many variables, including free text search. That is, there are virtually endless possibilities. Therefore, Therefore, I think the use of cache will actually be impossible.

On this page there is a suggestion for a solution based on update time ... interesting.

I can't really help you with the decision here as I have no idea what kind of data you want to display.

If this data is changing so frequently, does it even make sense to display it? Does it make sense to display all of it or wouid it enough to display the "latest" X entries - as they are "latest" at the time of request.

Thank you iionly, you are right.

We display the latest (i.e. sort DESC), log the time of the last one and the next page will start from that time downward. Of course, we miss those records that was changed in between, but we do not have records that may appear twice or more.

Simple experiment – Excellent results

Disclaimer: May upset Relational DB experts :)

In our case we have 13 filters, each is based on one metadata. For each filter we need 2 Joins (for the metadata and metastring tables) and Wheres with 2 sections (for the metadata name.id and the value). All together 26 Joins and 26 wheres., some of them are quite heavy like calculation the distance between two geographical point on the globe.

However, the page is very slow with 7 filters, and dead with 10 or more.

So, we did a simple experiment. We added a table to the Elgg database with foreign key e.guid and duplicated the relevant metadatas as columns in the new table. The query is composed of one Join (with elgg_entities) and 13 Wheres.

Now the page loads in about a second.

Of course, this is not the best practice but it may be a solution.

We would appreciate any reflection on this experiment.

Is it safe?

Are there other alternatives?

Do future versions of Elgg have solutions to such problem?

Thank you.

In Elgg 3 the normalization of the metadata/metastring table was removed. This will help in speeding up those kind of queries.

Time to update ;)

Thank you Jerome

Or creating a user just for the purpose of transferring all content formerly owned by accounts to be deleted to. Maybe easier to handle it this way if the content is supposed to be accessible on site as some plugins might rather expect the owner being of type user and not site.

As for the garbage collection: the plugin searches the database for orphaned data (I think maybe mostly metadata/metastrings). If these database entries have an owner_id pointing to an entity no longer existing it gets removed. That's a rather short explanation (that might even in parts be wrong as I've not looked into the code to see what exactly is done). Anyway, the hook might not help to prevent deletion because the garbage collection would rather use raw MySQL code instead of making use of Elgg API functions (that would call the hooks).

Thank you iionly.

From your answer I understand Jerome's comment about the Garbage collection plugin. The prevent deletion hook can create orphans which may be deleted by the garbage collection. So, this approach is not recommended.

 Or creating a user just for the purpose of transferring all content formerly owned by accounts to be deleted to. Maybe easier to handle it this way if the content is supposed to be accessible on site as some plugins might rather expect the owner being of type user and not site.

I do not understand this comment. Could you please explain it?

Do you think that making the objects owned/contained by the Site is not advisable?

Thank you very much.

We can put the owner and container in in metadata

Thank you Jerome.

PS: please try updating to Elgg 3.3 then you don't have to worry about subtype id's and metastring id's

As one member responded lately to your suggestion "I'm afraid". I have many plugins and many features. It will take me long time to update. I cannot do it right now, but I heard from you that Elgg 4 is coming soon. So, hopefully I'll go directly to Egg 4. 

Is it possible to go from 2.3 to 4 ?

@reuven you will need to upgrade to 3.3 first (at least for Elgg) and then go to 4.x. This can be done on the same day, but you need to do both steps.

Thank you Jeroen

Have a look at the documentation here: http://learn.elgg.org/en/stable/guides/database.html?highlight=sizes#entity-icons

Be aware that this information is for Elgg 3. The icon sizes hook also exists in Elgg 2.x, but the thumbnails are only generated when uploading a new icon. In Elgg 3 they are generated on demand.

Thank you Jeroen

This mischievous hook on Elgg 2.3 works well --> elgg_register_plugin_hook_handler('entity:icon:sizes', 'object', 'set_custom_icon_sizes')  :)