Dolt doesn't use indexed lookup on top N query. Very slow query resulting in DoltHub timeouts
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
Description
Dolt `v1.3.0` on Hosted Dolt.
DoltHub uses a table called `password_attempts` with the following definition:
```
| password_attempts | CREATE TABLE `password_attempts` (
`id` varchar(36) NOT NULL,
`user_id_fk` varchar(36) NOT NULL,
`event_type` int,
`ip_address` varchar(255),
`user_agent` varchar(3096),
`created_at` timestamp(6),
PRIMARY KEY (`id`),
KEY `password_attempts_user_id_fk` (`user_id_fk`,`created_at`),
KEY `user_id_fk` (`user_id_fk`),
CONSTRAINT `1jjafe08` FOREIGN KEY (`user_id_fk`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
```
This table currently has ~480k rows.
This query takes > 4 seconds to complete in Dolt:
```
SELECT * FROM password_attempts WHERE (user_id_fk = 'some-id') ORDER BY created_at DESC LIMIT 3;
```
This query take < 1ms in Postgres (a replica of the Hosted Dolt instance).
Here is the plan output:
```
mysql> EXPLAIN SELECT * FROM password_attempts WHERE (user_id_fk = '60deee19-e995-4250-b3f8-f9eb4308b909') ORDER BY created_at DESC LIMIT 3;
+---------------------------------------------------------------------------------------------------------------+
| plan |
+---------------------------------------------------------------------------------------------------------------+
| Limit(3) |
| └─ TopN(Limit: [3]; password_attempts.created_at DESC) |
| └─ Filter |
| ├─ (password_attempts.user_id_fk = 'some-id') |
| └─ IndexedTableAccess(password_attempts) |
| ├─ index: [password_attempts.user_id_fk] |
| ├─ filters: [{[some-id, some-id]}] |
| └─ columns: [id user_id_fk event_type ip_address user_agent created_at] |
+---------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.