Slow response ofdatabase Elgg 2.3.16

I'm working on a boosting feature which shows the boosted items (for fee) on the first page of 'All' or 'River'. Item's owner can characterize the users who will see the boosted item, e.g. gender, age-range, location, etc.
 
In addition, in some item types, (e.g. marketplace) users can select the type of items they want to see, e.g. category, price, vicinity, etc.
 
The feature works fine but when the user selects more than a few options the response is very slow.
 
The query is composed of a set of:
 
$options["joins"] = " JOIN elgg_metadata md_a on e.guid = md_a.entity_guid  JOIN elgg_metastrings ms_a on md_a.value_id = ms_a.id ";
$options["wheres"] = " md_a.name_id = {$metastring['aaa']} AND ms_a.string = 'aaa_value' ";
 
$options["joins"] = " JOIN elgg_metadata md_b on e.guid = md_b.entity_guid  JOIN elgg_metastrings ms_b on md_b.value_id = ms_b.id ";
$options["wheres"] = " md_b.name_id = {$metastring['bbb']} AND ms_b.string = 'bbb_value' ";
 
Etc.
 
Some of the "wheres" include MySQL functions which of course are slower.
 
The question is how to speed up the process.
 
Thank you for your help.
  • To much 'joins'

    The question is how to speed up the process.
     
    Try to optimize and tune MySQL
     
     
    Upgrade to Elgg 3.
     
    Test and test again
  • Thank you Nikolai

    Very useful

  • 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 ;)