let's talk about indexes baby - let's talk about all the good things they could be
This is intended as a discussion and not necessarily a core change - sites can add their own indexes so this is more of a 'what have you added, what works' discussion post
I have a search that returns about 177k results based on a combination of payment type & receive date.
Recent changes & in progress changes have gotten the time to render the search results page down from about 4 minutes to about 30 seconds.
However, I can get it down to 10 seconds by adding an additional index - a combined index on
payment_instrument_id + receive_date - ie
ALTER TABLE civicrm_contribution ADD INDEX payment_instrument_receive_date(payment_instrument_id, receive_date);
(note I'm comparing page render with page render not query time as more than one query could be affected)
The more indexes you have the longer it takes to write but I don't have any metrics on how much longer. It also takes up more disk space - some of our indexes are multiple GB.
OTOH they can make it quicker to retrieve.
My current thinking is to
- add receive_date & possibly contribution status_id to payment_instrument id index
- add receive_date to contribution_status_id index