Query optimization and database indexes

Question

I noticed that there a handful of queries that ExpressionEngine runs that don't use any database indexes. An example is the query SELECT DISTINCT ee.* FROM exp_extensions ee WHERE enabled = 'y' ORDER BY hook, priority ASC, class which gets run on every page. Doing a MySQL EXPLAIN on this shows that it could benefit from a database index. However, since the number of extensions installed is usually fairly small, adding an index for this query might not be a huge win.

I realize that the official stance is to not add indexes because it could conflict with future updates. On the other hand, from a practical standpoint, I have to balance resource usage and page rendering speed against the official stance; also, I'm willing and able to removing indexes before upgrading to a newer version of ExpressionEngine.

Is there a set of queries that are prime candidates for database indexes that would reduce load on the MySQL server? Is there any best practice on this?

Answer 1

I'm not aware of any established best practices for adding indexes to DB tables. I'd say the official EllisLab stance is you shouldn't have to add any because they've done this work for you where needed.

But every project has different needs and developers have to evaluate this on a case by case basis depending on the project. In my years with EE, I've only needed to add one index to a large database that was struggling with a specific query. I'm not well versed in this area so perhaps I've needed it more and just didn't know. If you're going to add indexes to any table it will probably be the exp_channel_data table.

My advice is unless you are having specific resource issues with your DB server or can identify a specific query that is struggling, there shouldn't be reason to add an index. EE runs fine with its default DB settings for more projects.

A quick solution if you are having specific issues is to implement the CE Cache addon. The speed increase when using the Static Driver is incredible... night and day difference in page load times... and it will immediately reduce any resource strain on the DB.

Answer 2

I haven't heard of any best practices around this. I think it depends on the user, you. If you're looking for some best practices to improve speed, you may want to start with:

• Ellis Lab's Handling Extreme Traffic
• Solspace's Website Performance Guidelines (PDF)
• Matt Perry's How to Make EE Faster
• .Net's Optimise your ExpressionEngine site

Generally speaking, I've heard of developers adding indexing for queries that are frequently used, that can benefit from them. I'm not certain if they drop the indexing before upgrades.

Answer 3

Most of required indexes are already there.

What I usually do, is if I use custom sorting order with exp:channel:entries I add key on field that I sort by.

I.e. if I have {exp:channel:entries orderby="title"} I add index on title in exp_channel_titles table.

If I have {exp:channel:entries orderby="my_custom_field"} and my_custom_field is a custom field with ID 7 I add index on field_id_7 in exp_channel_data table.

Answer 4

For custom field columns that will be used in join statements I find its best to set the column type to an int or a varchar(#) and then index it as suggested.

You can also optimise the way EE gets its data from the database.

When EE queries the database for a set of channel entries it brings back a lot of data that is often not needed.

This includes joining all columns from exp_channel_data, exp_members, exp_member_data and exp_channels for the related data.

Changing the way that EE generates its queries for the Channels module will yield some impressive results.

I had fun changing the channel entries query by adding a new extension hook to the build_sql_query method and adjusting the returned columns and table joins using tag parameters.

While working on a big site pulling lots of database content in from many {exp:channel:entries} tags I found it necessary to create an add-on to better manage the size of returned database results and keep the memory footprint smaller. Adding SQL comments via a tag parameter is a small bonus to make SQL debugging much easier when searching the MySQL logs.

If you're interested feel free to take a look: http://ee-garage.com/nsm-turbo-channels.

Please note that this version is rather experimental, hasn't been updated in a while and was never officially released.

Hopefully it will give you some ideas on how you can make EE's database functions run faster for you.

Answer 5

EE has a lot of indexes already, so it's fairly rare to run into a situation where you'd need another. However, I see no reason to avoid doing it if you see measurable performance gains in a custom query or something. You can always drop it later if something changes.

I got a very modest performance boost on my site by adding an index to the file_name column of exp_files. The file field tag pair can get a bit slow without that index if you have a huge number of files.

As far as your exp_extensions example goes, it's probably a bad candidate because there are only two options for enabled (y/n). It might be beneficial if you had thousands of rows and only a few were marked 'y', but as a general rule of thumb it's best to use indexes on columns with lots of unique values.