It looks like there are several redundant or partly redundant indexes that civicrm creates on the database. Some of this may be storage engine specific (e.g. InnoDB), but some isn't.
(see SE issue for more information)
To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information
Child items
...
Show closed items
Linked items
0
Link issues together to show that they're related.
Learn more.
It's also a really bad index because entity_table has very little variation (cardinality) so putting it at the beginning makes the index inefficient. It makes sense to drop index_entity and re-order the unique key to have entity_id at the beginning (or contribution_id at the beginning & have a separate index on entity_id)
From Gordan: "My solution is "drop the redundant index". I don't know enough about the inner workings of civi to propose a better, wider fix and usage review at this time."
@BjoernE what opinion is required here? There is no plausible reason why any of the indexes mentioned in the original report on SE could possibly be useful. They are making writes to the affected tables slower. Is there a remotely valid reason why those redundant indexes should not be removed?
Maybe "opinion" isn't the right word. It looks like you've done some great analysis but somebody has to volunteer to turn it into a pull request, confirm the issue and test it, and then get someone else to review it. And it's competing with all the other things on everyone's list. If you're able to make a pull request against https://github.com/civicrm/civicrm-core then that would push it one step further.
I've put up this to remove the indices from the above sleuthing that I think can be removed from core without doing performance testing. I've left the 2 that I think require further digging
@jaapjansma@KarinG the ones in your extensions also fall into the 'patently obvious' camp and you might want to remove them. Ditto cividiscount - althought I don't know who will find the enthusiasm to do a PR on that one
All the extensions I developed are community extensions and fixes and improvements are always welcome.
So if there are any extensions which need a change feel free to create a merge request.
@colemanw I totally agree Eileen does more than her fair share and I do not think @jaapjansma meant to say that she is deficient in any fashion!
I think all he meant to say is that we do not hold intellectual property on Civirules and do not feel it is "ours". We feel it is now the community that "owns" it just like we feel the community "owns" CiviCRM. Happy to do our bit (we do fund a yearly CiviRules issue sprint) but we do tend to be very sensitive to the suggestion that CiviRules is our software and we are therefore responsible for any deficiencies during the complete lifecycle without ever expecting any payment or funding. If we overreact sometimes I apologize wholeheartedly but hope for some understanding
When I read @jaapjansma's comment the message seemed to be "patch welcome."
I think that's a very good thing to say in some circumstances, for example if a user is asking for a new feature. And especially if they are not offering to pay for said feature, then a "patch welcome" response can help orient them to the nature of FLOSS and teach them that community members are expected to give back. It also draws an important personal boundary, teaching them that you don't work for them for free.
But in this context, the comment didn't feel right to me. For one thing, Eileen does give back, and doesn't need to be taught about FLOSS or how to be a good citizen. For another, she isn't asking you to work for her, this isn't even her issue. She is volunteering her time to analyze this issue, respond to the OP, generate a core patch, search through the universe of extensions for similar issues, and ping their authors as a courtesy. In my mind, a good response to such a courtesy would be "thank you."
I know we all work hard in this community. We all give a lot of "extra" and don't always get the thanks we deserve. I appreciate you @jaapjansma and @ErikHommel - you've contributed some great extensions, organized sprints, supported the community and the Core Team. You do a lot and I'm sure you don't get enough recognition and perhaps you're feeling frustrated about that. Let's all just try to keep a spirit of goodwill and courtesy in our communications with each other.
@colemanw no frustration on my side nor lack of recognition. Eileen is a shiny example to all of us and an inspiration. Totally second the spirit of goodwill and courtesy and apologize if anything else was conveyed. It was unintentional :-)