Thread

I wonder if there are any risks to adding new indexes on ExpressionEngine tables in MySQL? Will / can these indexes get dropped during EE upgrades? Will / can these indexes interfere with EE upgrades?

As some background: I understand the principles of database indexes, etc., so my interest in adding them is generally informed.

One of the ExpressionEngine sites we created for a client uses some custom queries on most pages, using exp:query. They work fine, but there’s a question from the client about query performance (it’s a long story—note that the site uses EE caching, and so the queries aren’t actually executed all that often).

In any case, I can improve the performance of these queries by adding new indexes in MySQL, for example, adding an index on parent_id makes an obvious difference on this query:

SELECT a.cat_id, cat_name, cat_url_title, cat_description, cat_image FROM exp_categories as a where parent_id = 68

Are there any downsides / risks to adding additional indexes like this? Does anyone else do this?

Any alteration to the database will carry some risk of interfering with the upgrade (or otherwise).  I’d be hesitant to suggest doing so unless you’re very familiar with mysql.  And even then- if you alter the db, it would be a good idea to check all version upgrades for conflicts.  The odds of an index being dropped would be remote.  It wouldn’t be impossible for an update to add an index and run into a conflict or otherwise alter the landscape.

But if you’ve the expertise, it’s ‘proceed at your own risk’.  There can be cases where the unique needs of the site make a good case for it- but only if you really know what you’re doing and can fix things if they go wrong.  And if you do make any alterations, you’d want to alert us if you run into a problem and need to post to the forum.

Hrm- I’m not remotely certain that clarified things.  (What I’d probably do- IF the site is in need of optimization- add the index.  See if it makes a truly significant difference.  If it doesn’t- easy to drop it.  If it does…. time to review the pros and cons again.)

Thanks Robin - I basically imagined there were two possible answers: OMG don’t do it because of X! and, It’s probably OK for now, but no guarantees in the future. So, your answer suggests the latter.

Since this is a site for client, if I do end-up needing to add indexes for them, for sanity’s-sake, I’ll probably write two little SQL scripts: one to add the indexes, and one to drop them. I could then more easily drop the new indexes before any upgrade, and re-add them after, just to be safe.

jayf,

Your plan sounds like a good way to go. I am closing this thread out for you but if you have any more questions feel free to start a new thread.