MySQL 8 slow queries on indexed table with WHERE event = ? AND merchant = ? AND created_at >=?

Question

I’m troubleshooting slow queries on MySQL 8 and need advice.

I have a table salla_events (~1M rows). The queries look like this:

SELECT * 
FROM salla_events 
WHERE event = 'order.created' 
  AND merchant = 2021223349 
  AND created_at >= '2025-08-15 22:09:20';

According to the slow query log, each query takes between 7–11 seconds and scans ~1.1M rows.

I’ve already created indexes:

CREATE INDEX idx_event_merchant_created 
  ON salla_events (event, merchant, created_at);

But the slow logs still show Rows_examined: 1120350+.

Here is part of mysql slow log:

User@Host: lamour_bot[lamour_bot] @ localhost [127.0.0.1] Id: 424571 # Query_time: 9.989759 Lock_time: 0.000005 Rows_sent: 5 Rows_examined: 1120379 SET timestamp=1755371566; select * from salla_events where event = 'order.created' and merchant = 1056778926 and created_at >= '2025-08-15 22:12:46';

Why is MySQL ignoring or underusing this index?

I’m using MySQL 8.0 with InnoDB and the queries are run frequently from a Laravel app.

Any advice on why this indexed query is still slow would be appreciated.

If event = 'order.created' matches millions of rows, MySQL’s optimizer may think the index isn’t selective enough and fall back to scanning. also table stats may be off and the optimizer may decide upon a bad plan. Lots of inserts/deletes recently? and lastly: What does "EXPLAIN SELECT ..." show so we can see what the optimizer is doing? — xQbert
– xQbert, Commented Aug 20 at 21:14
Try this query: SELECT COUNT(*) FROM salla_events WHERE event = 'order.created' AND merchant = 2021223349 AND created_at >= '2025-08-15 22:09:20'. What is the count of rows that match the query conditions? — Bill Karwin
– Bill Karwin, Commented Aug 20 at 22:05
It's using the index. The problem is that SELECT * has to go to the table rows to fetch the rest of the data, and that takes time with 1.3M rows. This is why SELECT COUNT(*) is fast, but SELECT * is slow. — Barmar
– Barmar, Commented Aug 20 at 22:22
This query returns 1.4M rows. What fraction of the table is that? — Barmar
– Barmar, Commented Aug 21 at 0:19
How many rows there are in the table totally, and how many rows matches each separate condition from WHERE? Test does the columns reordering to (merchant, event, created_at) helps. Test index forcing. — Akina
– Akina, Commented Aug 21 at 4:14

Rick James · Accepted Answer · 2025-08-21 19:53:30Z

0

In this type of table, it is best to have something like the following instead of the added idx_event_merchant_created:

PRIMARY KEY(merchant, event, created_at),

INDEX(id)

This clusters the data around the merchant, which is probably involved in the typical query. The schema you have zips through 1.1M rows in the index, but then has to do a BTree lookup for each row to get the rest of SELECT *. My suggestion zips through 1.1M desired rows without the extra lookup.

Note that getting COUNT(*) only needs to zip through the index, hence it is so much faster.

If you don't need all the columns, spell out just the desired columns. Eliminating TEXT columns may be another speedup.

answered Aug 21 at 19:53

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL 8 slow queries on indexed table with WHERE event = ? AND merchant = ? AND created_at >=?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related