Numbers quickly became one of the trickier parts of tracking and calculating mining profit. MySQL offers many different numeric types and I first settled on using DECIMAL for most of my calculations. Some ridiculous values like with 40 significant digits. That worked out fairly well at the start.
The cryptocurrency market however was in a downward spiral and exchanges started trading in fractions of a Satoshi. First 1/10, then 1/100 and 1/1000th of a Satoshi for certain high volume coins like Qwertycoin are being traded at 0.0000000048 BTC or 0.48 Satoshi. All of the major Cryptonight coin exchanges only had BTC pairs at the start of my project but they soon added LTC and XMR pairs. Those pairs drove prices down again where you see BBSCoin trading at 0.00000001 LTC. That is 0.0000000000969 BTC or 0.00969 Satoshi per coin.
I had a problem now. I did all my calculations at per hash per second. My numbers were getting larger and larger and something was wrong on the site as well. JavaScript was choking on the calculations I performed client side and throwing out tons of precision resulting in odd sorting and results. When both of these things happened at the same time I quickly realized I had more than digits to track than DECIMAL could handle.
TEXT to the rescue! Yes I hear you cry that TEXT isn’t as good for this or that reason but it could store all the precision I wanted. When I go to sort in MySQL I can cast that value as I need too. On the JavaScript side I settled on decimal.js for all calculations turning the string values into usable numbers and allowing for order to be restored. In my Python calculations I also starting using the decimal library to maintain precision.
In the end my largest numbers stored are in my exchange logs tracking the pair prices for coins other than BTC. The longest field is 74 characters long. So 73 digits total assuming all of them have a decimal point.
0.000001889999999999999903886019510246274677456312929280102252960205078125
A small precise number I didn’t feel like optimizing.
Using TEXT might be lazy but it bought me precision and removed having to think about updating my schema when new coins came out that were even crazier in terms of block rewards and fractional LTC values. In the end any trade off I think was worth it because I fixed my problems and could move on to other issues while removing a potential failure point in the future.
This was part of a series: Ephemeral Projects as Performance Art