# 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

If you target one of the FTS indexed columns in your queries, use the [CONTAINS](https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql) or [FREETEXT](https://docs.microsoft.com/en-us/sql/t-sql/queries/freetext-transact-sql) predicate functions rather than the infamous LIKE, which is a performance killer on tables with millions of records.

The following tables have been full-text search enabled:

## A. Comments

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')
```

#### Searching json\_metadata <a href="#json_metadata-the-ali-babas-cave" id="json_metadata-the-ali-babas-cave"></a>

Among all the columns found in the [Comments](/technical-informations/state-tables/comments.md) table, one that is more and more used is the `json_metadata` column.&#x20;

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 like

```
SELECT
    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!**

## **B. TxTransfers**

Full-Text Search is enabled on the `memo` column of the [TxTransfers](/technical-informations/operations/txtransfers.md) table

This will allow you to use the CONTAINS() and FREETEXT() predicate functions to find transfers containing a specific string in their memo.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.hivesql.io/technical-informations/full-text-search.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.