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.
Current indexes
KEY UI_contrib_payment_instrument_id (payment_instrument_id),
KEY index_total_amount_receive_date (total_amount,receive_date),
KEY index_source (source),
KEY index_contribution_status (contribution_status_id),
KEY received_date (receive_date),
KEY check_number (check_number),
KEY index_creditnote_id (creditnote_id),
KEY FK_civicrm_contribution_contact_id (contact_id),
KEY FK_civicrm_contribution_financial_type_id (financial_type_id),
KEY FK_civicrm_contribution_contribution_page_id (contribution_page_id),
KEY FK_civicrm_contribution_contribution_recur_id (contribution_recur_id),
KEY FK_civicrm_contribution_address_id (address_id),
KEY FK_civicrm_contribution_campaign_id (campaign_id),
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
@seamuslee @pfigel @sluc23 @davej @mfb have you guys tinkered with your indexes at all?