A client of ours is looking into adding a custom index to the EPiServer 5 R1 database to speed up the response from one specific query that is quite slow. (Yes, it's a FindPagesWithCriteria). I would normally recommend that they instead cache the result but in this case they where after a quick fix (as we all are at some point) and by adding an optimized index on tblProperty the query came down from ~1000ms to 14ms.
I know that R2 added new indexes but I suspect that they won't beat an index optimized for this exact query.
What I now wanted some input on was what potential side effects this might have. I could only come up with two:
Can anyone add (or remove) anything to this list?
Not really, that would be the obvious side effects.
Someone will forget about the index after some time, and you will get in trouble during upgrades (I recently spent half a day cleaning up indexes that crashed the migration tool.)
Go with the index as a real short term solution, and rewrite the FPWC code to perform better (limit the number of pages it returns). Also, R2 SP1 added substantial performance improvements to FPWC which is worth checking out. Just don't forget to remove that index!