Adding database indexes

When taking a closer look at phpmyadmin advisor after tuning the database settings, almost all advices are focussed on adding indexes to tables. The issue is:

"There are too many joins without indexes."

I can start adding indexes on those columns I suspect are hit most, but I'd like to make a more informed decision here.

What tables or columns should be indexed ?

The most really slow query is search, by far and also the most used (slow query). I gues that is true for everyone with a substantial number of entitities.

  • @RvR That's not a good functional plugin, just a thing I was hacking around in past. I didn't ever finished it up. I don't recommend it ;-) I've marked it to avoid confusion in future.

    Have a look at Evan's plugin for sphinx instead: https://github.com/ewinslow/elgg-sphinx

  • I worked on Evan's plugin. That is also not nearly finished. It doesn't respect access_id for one. Why are we discussing search for years and built several incomplete plugins instead of rebuild the one that really works, but just is slow. Damn !

  • I think I've seen some other plugin for solr or sphinx around, but couldn't find it. Have a look for a elasticsearch as well, it also runs on Lucene, so the same backend as solr.

  • @Paweł Sroka Just I've mentioned about it ;) Sphinx is a good. I'm use it. But we've some issues:

    Search seems to crash (Elgg Fatal Error) when the result set contains items you don't have access to.  (Administrators don't get this crash since they have access to all).

    On our site we allow the creation of closed, private and hidden groups.

    So this is not a panacea also ;)

  • The one I built works awesome, and does respect access_id - I'll do my best to get it generalized as it sounds like there is a demand :)

  • That would be awesome. I worked on so many search plugins, none so far met any reasonable requirements.

  • There is a huge demand since that's one of the biggest things Elgg is missing.

    @Matt is yours SOLR or Sphinx? In case it's Sphinx, it would really help if you could share your config file.
    At the moment I'm only indexing 'public' acces_id's, and I could settle with that, but my search engine is far from finished since it's so huge and has so much custom profile fields to be searched on, but it's working though.

    @Gerard maybe we could combine our ideas and do something for the community?

  • I've done a blitz on it and removed all the specific custom stuff, I haven't released it here yet as this is still entirely untested until I can get a test solr instance up and running to give it a full run-through.

    For anyone interested or already set up to test it please feel free in the meantime.

    https://github.com/arckinteractive/elgg_solr

  • Very cool Matt. I've downloaded it, but cannot give any feedback yet since I need to setup SOLR first.

Performance and Scalability

Performance and Scalability

If you've got a need for speed, this group is for you.