However, after analysis, I decide to pause it because the query to get the most popular accounts is very slow - it takes more than 20 minutes to retrieve results.
On the other hand, the query to get the most blocked accounts is fast - less than a minute - which makes it feasible to do so.
When a query takes ages, the SQLite suffers a known database lock problem. SQLite is not very good at concurrency - when a thread is reading all rows, all other write operations (such as insertion) will be locked:
1 2 3 4 5
database is locked Traceback (most recent call last): File "test.py", line 135, in <module> con.commit() sqlite3.OperationalError: database is locked
Almost Same query - but with different parameters. For ‘most blocked’ the filtering is where what = 'ignore' and for ‘most popular’ the filtering is where what = 'blog'
This is the table schema and indices:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
sqlite> .schema CREATE TABLE mute ( who text, whom text, what text, time text, block integer, constraint pkey primary key (who, whom) ); CREATE INDEX index_from on mute(who); CREATE INDEX index_to on mute(whom); CREATE INDEX index_time on mute(time); CREATE INDEX index_what on mute(what); CREATE INDEX index_block on mute(block);
And if we run the query to see the number of the candidate rows:
1 2 3 4
sqlite> select count(rowid) from mute where what='ignore'; 332096 sqlite> select count(rowid) from mute where what='blog'; 100757608
It is a huge difference! Yes, for both queries, SQLite will utilize the index what and process the result set by grouping - the more rows - the more time it requires!
This can be confirmed by the explain statement which takes you how the SQLite will process your query.
SQLite is a relational database - which makes it inefficient in handling a large dataset. The steem blockchain has been running for years - the data accumulates quickly.
On Steem Blockchain, the reputation can be formatted in a logarithmetic (base 10) scale. Given recently, my PR to fix the reputation formatter in SteemJS is merged, I also translate this to PHP which I need.
Have you heard of the concept of “Compound Effect”?
Here is the definition
“The Compound Effect is the principle of reaping huge rewards from a series of small, smart choices.” This idea behind The Compound Effect is that small consistent action over a long period of time has greater pay offs than intensely large but short changes.
A simple example is that if you take 30 minutes exercise everyday and stick to it no matter what happens, you will start losing weight gradually in a few months time. Rome is not built in a day. Don’t expect to see the outcome the next day.
It is hard, but effective. As a human, we tend to find execuses to be lazy. Fewer people choose to be hard-working and be patient.
I have been practising coding (and solving more than 800 puzzles) on leetcode for more than a year now. I do it every day - no execuses - and I also post articles on how to solve them on my blog. As a result, I am always quick to code.
I also do the rope skipping (1000 times) per day for 3 months, and also the intermittent fasting i.e. strictly eating in a 8 hour window per day. And I have lost around 3kg so far.
Have you got your “Compound Effect” to share? Comment below and receive upvotes!
Every little helps! I hope this helps!
Steem On!~
If you like my work, please consider voting for me, thanks! https://steemit.com/~witnesses type in justyy and click VOTE
Alternatively, you could proxy to me if you are too lazy to vote!
function loadPriceBittrex(callback, retries) { // Load STEEM price in BTC from bittrex and convert that to USD using BTC price in coinmarketcap request.get('https://api.coinmarketcap.com/v1/ticker/bitcoin/', function (e, r, data) { request.get('https://bittrex.com/api/v1.1/public/getticker?market=BTC-STEEM', function (e, r, btc_data) { try { steem_price = parseFloat(JSON.parse(data)[0].price_usd) * parseFloat(JSON.parse(btc_data).result.Last); log('Loaded STEEM Price from Bittrex: ' + steem_price);
if (callback) callback(steem_price); } catch (err) { log('Error loading STEEM price from Bittrex: ' + err);
