My recommendation would be that you start by looking into the query planner explain command. I use the TSQL equivalent a lot when doing these kinds of optimizations to the eve DB. Unfortunately, I know very little about the details of innoDB, but as an example, consider the following query:
UPDATE signatures SET modifiedByID = 0, modifiedByName = "Tripwire" WHERE lifeLeft < NOW() AND lifeLength <> '0' AND type <> 'wormhole';
When executing that query, you’re hinging on the index being created by this key in the signatures table to avoid a whole table scan:
KEY `lifeLeft, lifeLength` (`lifeLeft`,`lifeLength`),
However, it may be buying you less than you expect. You’re about to wander into the deep magic of implementation-specific db engine index construction techniques.
Here are some good links on the topic:
https://www.sql-server-performance.com/composite-indexes/
https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html
It’s hard to say exactly what this is doing without looking at the query planner results. However, my guess is that because you also have the “type” in the where clause, the composite index is less useful than you might think. The composite index can be used to rapidly assemble a bunch of rows, but it’s possible that they now need to be merged and scanned to see if they are of type wormhole. It’s possible the innoDB engine is ignoring the index entirely and just doing a full table scan, because it considers the merge-and-filter to be more expensive than the table scan.
My recommendations would be:
- Throw some of these things through the EXPLAIN statement and see if it tells you where the bottlenecks are.
- Try making a triple clustered index that goes (type, lifeLength, lifeLeft) and see if it makes the updates and deletes magically faster.
Note however that if #2 does magically work you probably want to consider the situation a bit more and not just blindly leave that there. A triple index in that order might make your inserts much slower. Use the query planner!