About RealTime tables

RealTime tables in ManticoreSearch are similar to regular database tables, where documents can be added, modified or deleted using INSERT, REPLACE, UPDATE, or DELETE statements.

RealTime tables are designed to provide fast indexing and querying of data with minimal delay between the moment a document is inserted or modified and the moment it becomes available for searching.

When creating a RealTime table, you need to specify the path where the table files will be stored, and define at least one full-text field and an attribute.

table rt
{
    type = rt
    path = /usr/local/sphinx/data/rt
    rt_field = title
    rt_field = content
    rt_attr_uint = gid
}

Here's a simple example:

CREATE TABLE testrt (title TEXT, content TEXT, gid INT);

The table will have 2 text fields and an integer attribute. The document 'id' is implicit and does not need to be declared separately.

Inserting documents

First, let's connect to Manticore using the mysql client:

mysql -P9306 -h0

And create the table:

CREATE TABLE testrt (title TEXT, content TEXT, gid INT);

We can see it was created:

SHOW TABLES;

It's schema can be viewed with 'DESCRIBE' command:

DESCRIBE testrt;

Inserting documents is done the same way as inserting rows in a database table.

INSERT INTO testrt(title, content, gid) VALUES('List of HP business laptops','Elitebook Probook',10); INSERT INTO testrt(title, content, gid) VALUES('List of Dell business laptops','Latitude Precision Vostro',10); INSERT INTO testrt(title, content, gid) VALUES('List of Dell gaming laptops','Inspirion Alienware',20); INSERT INTO testrt(title, content, gid) VALUES('Lenovo laptops list','Yoga IdeaPad',30); INSERT INTO testrt(title, content, gid) VALUES('List of ASUS ultrabooks and laptops','Zenbook Vivobook',30);

Multiple documents can also be inserted in a single INSERT statement:

INSERT INTO testrt(title, content, gid) VALUES('List of Acer gaming laptops','Predator Helios Nitro',45),('List of Acer budget laptops','Aspire TravelMate',55),('List of Acer ultrabooks','Swift',55);

Executing search queries

Now we have some data, we can do some queries.

Fulltext searches are done with the special clause MATCH, which is the main workhorse.

SELECT * FROM testrt WHERE MATCH('list of laptops');

As you see in the result set we can only get back the doc id and the attributes. The fulltext fields values are not returned since the text is only indexed, not stored also, and it.s impossible to rebuild the original text.

Now let.s add some filtering and more ordering:

SELECT *,WEIGHT() FROM testrt WHERE MATCH('list of laptops') AND gid>10 ORDER BY WEIGHT() DESC,gid DESC;

The search above does a simple matching, where all words need to be present. But we can do more:

SELECT *,WEIGHT() FROM testrt WHERE MATCH('"list of business laptops"/3');

SHOW META returns information about previous executed query, that is number of found records (in total_found), execution time (in time) and statistics about the keywords of the search.

SHOW META;

Performing changes

Updates are possible on existing data. UPDATE statement can be used to update attribute values.

UPDATE testrt SET gid=45 WHERE MATCH('Lenovo');

If full-text fields content requires changes, the whole document needs to be resubmitted using REPLACE statement

REPLACE INTO testrt(title, content, gid) VALUES('Lenovo laptops list','Yoga Thinkpad',30);

SELECT * FROM testrt WHERE MATCH('Thinkpad');