SteemSQL is gaining popularity among Steemit’s users who rely on it to create their posts full of statistics and those who use it to run their project.
The most often queried tables are the
Accounts and the
Comments table, the second one being the great winner. That’s not a surprise because this table contains all information about posts (and comments) and it is the main source of information.
json_metadata: the Ali Baba's cave
Among all the columns found in the
Comments table, one that is more and more used is the
json_metadata column. It is a bit of the stuff of the blockchain, where developers can freely store any information for their project.
As more and more information is stored by various project into this column, then comes more and more queries trying to extract data from it.
Full Text Search enabled
Queries issued against this column slowly became the source of a major slowdown of the whole infrastructure, because people had to use LIKE clauses or SQL’s native JSON parsing functions, which are not the best at performances, especially on huge tables.
Therefore, I added a Full Text Search index on the
Now, instead of writing a query like
SELECT COUNT(*) FROM Comments WHERE json_metdata like ‘%utopian-io%’
one can write
SELECT COUNT(*) FROM Comments WHERE CONTAINS(json_metdata, ‘utopian-io’)
The first query will take dozens of minutes to complete whereas the second one will complete in only 2 seconds!
A quick reminder
There are now 3 FTS enabled columns on the
If you target these columns in your queries, I urge you to use the CONTAINS and FREETEXT predicate function rather than the infamous LIKE, which is a performances killer on tables with millions of records.
Thanks for reading!