Wait! Don’t just run out and add a bunch of indexes to your database. Indexes add overhead. And too much overhead will undo any speed improvements you make with good indexes. Now while I’ve administered many MySQL databases, I wouldn’t dream to call myself a DBA. Get yourself a real MySQL DBA and have them analyze your Magento instance for improvements.
That said, we’ve found some improvement recently by adding the following indexes:
1 2 3 4
When we added these to our UAT environment, we saw the add to cart speed increase to match (if not exceed by just a little) our Production environment. To put that into context, our UAT environment is typically 3-4 times slower than Production. However, when we put these indexes into Production, we didn’t see as much of an impact to overall speed. What we saw instead was more consistency in user experience. Before the indexes, there were spikes and dips in the amount of time it took a customer to add an item to the cart. After the indexes the line flattened out (meaning the customers that were getting a “bad” experience no longer saw the slow add to cart. Instead they saw the same speed as the average customer did).
Again, your mileage may vary. So seek the advice of a MySQL DBA. We have a high-traffic site with around 8000 products (about 5000 of those are actually active) and we do coupons like nobody else.