Optimize queries for account_tx to work around SQLite query planner

[mtrippled]

No description provided.

[ripplelabs-jenkins]

Jenkins Build Summary

Built from this commit

Built at 20180101 - 05:16:58

Test Results

Build Type	Result	Status
clang.debug.unity	970 cases, 0 failed, t: 381s	PASS ✅
coverage	970 cases, 0 failed, t: 614s	PASS ✅
clang.debug.nounity	968 cases, 0 failed, t: 290s	PASS ✅
gcc.debug.nounity	968 cases, 0 failed, t: 307s	PASS ✅
gcc.debug.unity	970 cases, 0 failed, t: 431s	PASS ✅
clang.release.unity	969 cases, 0 failed, t: 470s	PASS ✅
gcc.release.unity	969 cases, 0 failed, t: 493s	PASS ✅

[mtrippled]

The unit test is failing because the manual filter threw off the LIMIT clause's results. I have to rethink this.

[mtrippled]

I rewrote the SQL in the original instead of making a manual filter. It passes unit tests and improves performance.

[nbougalis]

I'm no SQL optimization expert, but the new query should generate the same results and looks reasonable.

[sublimator]

How big is a full history sqlitedb these days? Is it even still possible?

[bachase]

👍

[ximinez]

Nit: Remove the . from the commit message.

[ximinez]

I'm honestly a little surprised that the implicit join (FROM AccountTransactions, Transactions) is faster than the INNER JOIN used in prefix, but data don't lie.

👍

[mtrippled]

@ximinez You're right that the original query should be slightly faster because it only joins on those 2 fields once, whereas the new version essentially takes the union of 2 distinct search queries that each join on those 2 fields. But the query planner for the original version wasn't picking up the index for one or both of the operands of the OR expression in the query.

[bradsavon]

This is referenced in https://ripple.com/dev-blog/rippled-version-0-81-0/ but not merged?

[mtrippled]

@bradsavon It will get merged into the develop branch, which will turn into 0.90. This was put into the current production code because it will contribute to stability right away.

[bradsavon]

Great, thanks for clarification

[seelabs]

In 0.90.0-b3