Terminal Terminal | Web Web
Home  //  Play

Manticore Introduction in RealTime indexes

Difficulty: Beginner
Estimated Time: 10 minutes

Manticoresearch - Introduction to RealTime indexes.

In this course you will learn the basics of RealTime indexes.

Manticore Introduction in RealTime indexes

Step 1 of 4

About RealTime indexes

A RealTime index is similar to a database table: they start empty and documents can be added, modified or deleted using INSERT/REPLACE/UPDATE/DELETE statements. Any update (new data or modified) is available almost instant for searching.

The most simple RealTime index declaration requires the 'path' to where the index files will be stored and definition of at least one full-text field and an attribute.

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

A simple Real-Time index creation looks like:

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

The index will have 2 text fields and an integer attribute. The document 'id' is implicit and it's not declared.

Inserting documents

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

mysql -P9306 -h0

And create the index:

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