11

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?

  • This is a really complicated question. I've got a "database best practices for EE" article in progress right now, but I don't know if it'll be finished by the end of 7 days. :) – adrienne Feb 27 '13 at 6:27
  • @adrienne Looking through your comments, you have the makings of the best answer so far, so I look forward to your article. Unfortunately, I can't control the rules of bounty, if that's the 7-day issue. – Bryan Feb 27 '13 at 15:37
  • Bryan, That's fine. :) I don't really care about the bounty; it'd be nice but it's not actually a big deal. – adrienne Feb 27 '13 at 21:05
3
+100

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.

| improve this answer | |
  • 3
    Disagree! Indices are really important for database performance, and EllisLab didn't add any to the default EE install. However, they're the sort of thing that is by and large best to tune to your particular situation. I really do have an article in progress about this very issue, among others. I'll see if i can knock it out in the next couple days. – adrienne Feb 27 '13 at 7:09
  • What do you disagree with? Sounds like we're saying the same thing. They are important but most sites won't need this kind of attention. It's something that is absolutely determined on a case by case basis. I look forward to reading this article. I can absolutely use some education in this area. – Anna_MediaGirl Feb 27 '13 at 7:13
  • 2
    What i'm saying is that most sites can benefit from indices, not just a few big ones. It's just that people need to be aware of how to tune them because if you add too many they can cause performance problems rather than alleviate them. – adrienne Feb 27 '13 at 7:36
  • 4
    It's not true that EE's default install is w/o indicies. It uses PRIMARY KEY, KEY or INDEX, two of the four available indices in MySQL. exp_channel_titles has the most KEYs and depending on your need, exp_channel_data probably has the most need for additional indices. But as both I and @Anna_MediaGirl mentions, there's not much standard about it. Slow queries aside, most speed is gained by Steve Souders' 80/20 rule. Reduce queries, reduce requests, consolidate, minimize, cache. Optimizing queries is just one part and works best when you identify frequently requested inefficient queries. – nonprofit_tech Feb 27 '13 at 8:06
  • 2
    In MySQL, KEY and INDEX are the same thing, are they not? I don't mean PRIMARY KEY obviously. Either way, I don't expect blindly adding indexes to EE would help you much unless you have been watching New Relic or something for a while and know specifically which queries are wasting the most time on your site. – Adrian Macneil Feb 28 '13 at 9:24
8

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:

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.

| improve this answer | |
4

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.

| improve this answer | |
3

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.

| improve this answer | |
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.