NewRelic was reporting a spike of slow queries all relating to adding an item to a wishlist. The stack trace shows a lot of entity loads/saves etc etc but what stood out was the use of the wishlist code field to pluck data out of the DB.
I noticed, that the code column isn't indexed. Out of the box, there are 6 indexes on the commerce_wishlist table, code not being included in one of them. After adding an index in for code we noticed the performance of our wishlist queries increase by 3 orders of magnitude.
So for a simple select query that WishlistStorage::loadByCode($code) performs, we saw query speeds of 441ms and after popping an index in, we got that time down to 0.1ms. A significant increase in speed!
Define a storage schema handler that defines an index on the code base field.
Add an update hook that informs Drupal of the new handler, which adds the new index.
Needs review
Code
Steven Jones → made their first commit to this issue’s fork.
I've improved the approach in the MR now, so instead of adding the index directly, we define a custom storage schema, and use the update hook to nudge Drupal to add the index.
Basically, the same net result, but doing it in the more 'Drupal way' as such.