Full Text Search
HiveSQL has several Full-Text Search (FTS) indexes, which speed up queries in an impressive way. They allow fast retrieval of information about comments and transfers
The following tables have been full-text search enabled:
There are 3 columns with Full-Text Search indexes:
1.
title
2. body
3. json_metadata
Query example
Let say I want to know if anyone mentioned the user "@arcange" in a post or comment, the following simple query will do the trick
SELECT
author, title, body, url
FROM
Comments
WHERE
author <> 'arcange'
AND CONTAINS(body, 'arcange')
Among all the columns found in the Comments table, one that is more and more used is the
json_metadata
column. It's the catch-all column, where developers can freely store any information about comments and their apps. As more and more information is stored by various projects into this column, then comes more and more queries trying to extract data from it.
Queries issued against this column are often a source of a major slowdown on the whole infrastructure because people have the bad habits to use the LIKE operator or SQL’s native JSON parsing functions, which are not the best at performances, especially on huge tables.
With the FTS index on the
json_metadata
column, instead of writing a query likeSELECT
COUNT(*)
FROM
Comments
WHERE
json_metadata like '%peakd%'
one can write
SELECT
COUNT(*)
FROM
Comments
WHERE
CONTAINS(json_metadata, 'peakd')
The first query will take dozens of minutes to complete whereas the last one will complete in less than 2 seconds!
This will allow you to use the CONTAINS() and FREETEXT() predicate functions to find transfers containing a specific string in their memo.
Last modified 10mo ago