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.

  • We should have all necessary indexes in place. If you have some problematic query, please provide it together with result of running EXPLAIN on it and brief info where did it come from.

  • I think anything where you join tables there is an issue. My biggest issue is entering a search query. It takes more  20 seconds to fullfill and that is with the default search plugin.

    Other high demand queries are custom made, like joined activity from multiple groups and friends suggestions based on location, common interest, groups and joined friends. Which offcourse is a heavy query when having more than let's say 200 friends. But let's focus on search first.

    How to improve search queries, using indexes.

  • If you don't provide technical details of your problem, we can't help you.

    You're talking about search query, so I ask you to give us here your problematic query SQL. To answer the question if there's problem with indexes or not, having result of EXPLAIN runned on this particular query is essential.

    Keeping this topis at this general level makes no point as you won't get good answer.

    To see how to create index, you may look here: http://dev.mysql.com/doc/refman/5.0/en/create-index.html but you should be able to google it yourself. There are downsides of having too many indexes as well as having joins may be actually computed in efficient way. There are a lot of pitfalls in constructing efficient queries, so checking how query planner treats particular query will tell us more than anything else.

  • Well I would if I could, how do I know what the query looks like of an ordinary search ?

    I do want to answer the question you raised, but I did not write the code for a search query. It is a core function, do you want me to find out how it is actually done in SQL ?

  • Search is the most inefficient query elgg has, a number of our larger sites we've had to disable core search entirely and replace it with SOLR or similar.

    I would like to fix up core search efficiency as a goal for 1.10

     

    You can view queries in elgg if you use the developer tools plugin, set it to log to the screen and set the lowest level of logging - you'll get a lot of logs at the bottom of the page including the SQL of all queries used to build the page.  Run a search for something gibberish that won't return any results, on the results page search for your gibberish and you should find the query pretty quick.

  • Ok, will do. If I find any quick wins there, I'll post them here.

  • @Matt , btw is there a SOLR/Lucene 1.8 plugin ? I found an old 1.6 one, but I guess you are not using that for new sites.

  • There's not a publicly available one that I'm aware of - the one I'm using is quite a bit too customized for the specific sites I'm running to be useful as a general thing, but I do want to pare it down to one that can be useful out of the box.

Performance and Scalability

Performance and Scalability

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