SteemSQL - New Full Text Search indexed json_metadata

in steemsql •  last month

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 json_metadata column.

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 Comments table:

  • title
  • body
  • json_metadata

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!


footer created with steemitboard - click any award to see my board of honor

Support me and my work as a witness by voting for me here!

You can also vote for my witness with SteemConnect

You Like this post, do not forget to upvote or follow me or resteem

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  Trending

WARNING - The message you received from @ayumanis is a CONFIRMED SCAM!
DO NOT FOLLOW any instruction and DO NOT CLICK on any link in the comment!
For more information, read this post: https://steemit.com/steemit/@arcange/phishing-site-reported-www-steemitfollowup-ml

Thanks for all your efforts!
Can you please direct me to info as to how I can connect to your steemsql?
Thanks

fab, thanks @arcange

In you sample query, "json_metdata" should be "json_metadata"

that is interesting, I do know database programming but bad with web things, maybe a start for me