Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cache initial SQL blocklist queries #6

Closed
brannondorsey opened this issue Apr 26, 2018 · 1 comment
Closed

Cache initial SQL blocklist queries #6

brannondorsey opened this issue Apr 26, 2018 · 1 comment

Comments

@brannondorsey
Copy link
Member

Blocklist queries on initial pageload and whenever a filter is applied are very costly to the SQL server. There isn't a lot we can do about the filter queries, but we could cache the initial pageload query as the results will only change each time we parse re-parse the blockchain (and review messages). In particular, here are the queries I intend to cache:

SELECT DISTINCT block_height FROM coinbase_messages_unique ORDER BY block_height;
SELECT DISTINCT block_height FROM address_messages_unique ORDER BY block_height;
SELECT DISTINCT block_height FROM op_return_address_messages_unique ORDER BY block_height;
SELECT DISTINCT block_height FROM coinbase_messages_unique WHERE valid = 1 ORDER BY block_height;
SELECT DISTINCT block_height FROM address_messages_unique WHERE valid = 1 ORDER BY block_height;
SELECT DISTINCT block_height FROM op_return_address_messages_unique WHERE valid = 1 ORDER BY block_height;
SELECT DISTINCT block_height FROM coinbase_messages_unique WHERE nsfw = 0 ORDER BY block_height;
SELECT DISTINCT block_height FROM address_messages_unique WHERE nsfw = 0 ORDER BY block_height;
SELECT DISTINCT block_height FROM op_return_address_messages_unique WHERE nsfw = 0 ORDER BY block_height;
SELECT DISTINCT block_height FROM coinbase_messages_unique WHERE bookmarked = 1 ORDER BY block_height;
SELECT DISTINCT block_height FROM address_messages_unique WHERE bookmarked = 1 ORDER BY block_height;
SELECT DISTINCT block_height FROM op_return_address_messages_unique WHERE bookmarked = 1 ORDER BY block_height;
@brannondorsey brannondorsey added this to the Public Web Release milestone Apr 26, 2018
@brannondorsey brannondorsey self-assigned this Apr 26, 2018
@brannondorsey brannondorsey moved this from To do to In progress in Public Web Release May 8, 2018
@brannondorsey brannondorsey moved this from In progress to Testing in Public Web Release May 8, 2018
@brannondorsey brannondorsey moved this from Testing to Done in Public Web Release May 8, 2018
@brannondorsey
Copy link
Member Author

Done.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
No open projects
Development

No branches or pull requests

1 participant