From reviewing some StackOverflow answers, I learned that MariaDB 10.8 supports ROW_NUMBER, which wasn't previously supported in MySQL 5.7. We can simplify this query while maintaining the same performance.
Depends on D4674
Details
Details
I first tested this stack locally in my environment to make sure the queries worked. Then I patched it onto my production keyserver and confirmed performance was improved
Diff Detail
Diff Detail
- Repository
- rCOMM Comm
- Lint
Lint Not Applicable - Unit
Tests Not Applicable
Event Timeline
Comment Actions
Looks great!
| keyserver/src/fetchers/message-fetchers.js | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 281 ↗ | (On Diff #15086) | Do we still need to support the old engine? This query contains code specific to MariaDB LIMIT 18446744073709551615 so it is not specific to mysql5.7. | |||||||||||||||
| 331 ↗ | (On Diff #15086) | What if two different messages have the same time? We can handle that in two ways: we can either add another order by column, e.g. id, or we can use RANK instead of ROW_NUMBER. The difference between these two is that when ROW_NUMBER will see an equal result, it will assign non deterministically different values, but RANK will assign the same value, but then the next value is increased accordingly. So if we order by a field f the following will happen
So overall, in our use case, it is safer to use RANK. At the same time, we should use ids to sort when the same timestamp is assigned. | |||||||||||||||
| keyserver/src/fetchers/message-fetchers.js | ||
|---|---|---|
| 281 ↗ | (On Diff #15086) |
Yes until all dev environments have been migrated.
That's valid, I could remove that patch. That said, I did see a StackOverflow comment yesterday implying it was generally good practice to apply this LIMIT. Given MariaDB's relationship with MySQL I'm willing to bet MySQL 8 may have the same requirement. Either way, I expect this code will be deleted soon. |
| 331 ↗ | (On Diff #15086) |
|