I have a very weird issue, and I can't wrap my head around it.
Given a rather big table (~5M transactions).
(1) ⚠️ I am running this query - this wallet has about ~20000 transactions in total:
SELECT * FROM transactions AS t WHERE t.created_at > "2023-04-01" AND t.created_at < "2023-04-02" AND (t.to_wallet_id = 6000 OR t.from_wallet_id = 6000) ORDER BY t.id DESC LIMIT 100
This results in 11 rows, taking 9300ms
EXPLAIN it tells shows that it used the
PRIMARY key (
id) as index.
(2) ✅ Running the same query with a
<=11 results into 934ms execution time.
(3) ✅ Running the query from (1) with
FORCE INDEX results into ~100ms execution time.
(4) ✅ Running the query from (1) without
ORDER BY results into ~100ms execution time.
(5) ✅ Running the query from (1) with another
wallet_id with significantly more overall transactions (~500.000) results into ~900ms execution time.
We have indexes for multiple rows and also multi-column indexes. However, it seems that MySQL is not using them, even though they are showing up in the "possible_keys" field when using
But even ignoring this, I am super confused why this particular
wallet_id performs so bad, given that it has 10% of the entries of the other
wallet_id. Also the fact that setting the
LIMIT to a value
<= 11 (lower than expected results) is showing the expected performance.
Things I considered already:
- We have indexes for all relevant columns and also some multi-column indexes (in the right order)
- We were on MySQL 5.x -> updated to MySQL 8.x (suspected this might have been the issue)