In this article, we will be talking about Manticore Search, which is an open-source search engine first released in 2017 as a fork of the Sphinx search engine. We will try to describe this search engine briefly, mention some of its differences from the Sphinx search engine, and we will provide a step-by-step guide on how to build full-text indexes from SQL Server databases. Finally, we will show how to connect to the Manticore engine from the SQL Server management studio using a linked server object. In our previously published articles in this series, we talked briefly about the Sphinx search engine and how to create full-text indexes from SQL Server databases.
Introduction
One important thing we forgot to mention in the previously published articles is that at the end of 2016, the Sphinx development and support was suspended and there was a risk that it would never continue. For this reason, some former Sphinx Technologies employees with the help of some companies that were using the Sphinx search engine started – in May 2017 – decided to develop a new search engine starting from a fork of the latest release of the Sphinx search engine (2.3.2 beta) in order to provide support for existing Sphinx clients, increase the product stability, and to continue development. This fork was named later “Manticore Search”. Note that in December 2018, Sphinx search engine development resumed, and version 3.0.1 was released, but it is not open-source anymore.
In this article, we cannot provide a full comparison between both products. But, a quick tour in the Sphinx search engine and the Manticore search websites let you know the difference between both companies’ productivity. In addition, the Manticore search website contains many comparisons between Manticore and Sphinx releases. As an example:
- Features comparison between Manticore Vs. Sphinx
- Sphinx 3 vs Manticore: performance benchmark
- Manticore 2.7.5 vs Sphinx 3.1.1
In the rest of this article, we will show how to install the latest version of Manticore (3.5.0) on Windows and how to build full-text indexes from SQL Server tables.
Manticore Installation
To download the latest release of Manticore, you should open the Manticore home page, go to the downloads page, beside the Windows icon, click on the 64-bit hyperlink.
Figure 1 – Download Manticore for Windows 64-bit
After clicking on the hyperlink, a message box appears asking if we want to start downloading or to copy the download URL.
Figure 2 – Download message box
After the download is complete, we created a directory named “Manticore” within the drive “E:”. Then, we extract the downloaded archive within this directory. The folder content should look like the following screenshot:
Figure 3 – Manticore extracted files
Now, we should install the “searchd” application as a service (Check the previously published Sphinx search engine installation section). To do that, we should open the command prompt as administrator and run the following command:
E:\Manticore\bin\searchd –install –config E:\Manticore\manticore.conf.in –servicename Manticore
Figure 4 – Installing searchd as a service
After running this command, we can check if the service is installed within the Services window:
Figure 5 – Service installed successfully
Now, if we try to start the installed Windows service, it fails, and the following error is shown:
Figure 6 – Windows service error message
To solve this problem, we should open the configuration file (E:\Manticore\manticore.conf.in) and replace all @CONFDIR@ occurrences with the fully qualified path “E:/Manticore” (make sure to use slash instead of backslash). Also, we should remove the “data_dir” property since it is related to real-time indexes. Besides, we should add the current MySQL version using the “mysql_version_string” property to avoid conflicts, and we should add the “binlog_path” property. The file content should look like follows:
searchd
{
listen = 127.0.0.1:9312
listen = 127.0.0.1:9306:mysql
listen = 127.0.0.1:9308:http
log = E:/Manticore/log/searchd.log
query_log = E:/Manticore/log/query.log
pid_file = E:/Manticore/log/searchd.pid
query_log_format = sphinxql
binlog_path = E:/Manticore/data/binlog/
mysql_version_string = 8.0.20
}
Now, if we try to start the service again, it will start successfully.
Figure 7 – Service started successfully
As mentioned in the first article in this series, MySQL client needs to be installed on your machine to be able to connect to the Sphinx console. The same condition applies to the Manticore search.
To connect to Manticore search using MySQL console, we should open the command prompt and go to the MySQL binaries directory (in this example the directory is “C:\Program Files\MySQL\MySQL Server 8.0\bin”) and use the following command:
mysql -P9306 -h127.0.0.1
Figure 8 – Connecting to Manticore using MySQL console
As shown in the image above, the server version is the Manticore search version (3.5.0).
Now, let’s execute the “show status” command view the server (Manticore engine) status. The result is as follows:
Figure 9 – Show status command result
Building indexes
To build an index, we will use the same commands provided in the previous article Building full-text indexes using the Sphinx search engine. (The database used in this experiment is the AdcentureWorks2017 database).
As it is already explained in the previous article, we will not explain each code block we must add to the configuration file (Manticore .conf.in) and the data preparation steps needed. We will pretend that you already took the previous guide.
The index is built for the following query:
select ProductID as ‘id’, Name as ‘name’, ListPrice as ‘price’, ModifiedDate_TS as ‘date’ from Production.Product
The following code must be added to the configuration file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
source base { type = mssql sql_host = .\DATASERVER sql_user = sql_pass = sql_db = AdventureWorks2017 mssql_winauth = 1 mssql_unicode = 1 } source product_base: base { sql_field_string = name sql_attr_float = price sql_attr_timestamp = date } source product : product_base { sql_query = \ select \ ProductID as 'id', \ Name as 'name', \ ListPrice as 'price', \ ModifiedDate_TS as 'date' \ from Production.Product } index product { source = product path = E:/Manticore/data/index/product/ charset_type = utf-8 } indexer { mem_limit = 520M } |
After adding this code block (index definition, indexer configuration), we should open the command prompt utility and run the following command to start building full-text indexes (make sure to run command prompt as an administrator):
E:\Manticore\bin\indexer -–all -–config E:\Manticore\manticore.conf.in –-rotate –-print-queries
Figure 10 – Building full-text indexes
If we take a look within the index directory (E:\Manticore\data\index\product), we will find that several files are created.
Figure 11 – Created index files
Based on the official documentation, these files store the index information as follows:
|
Extension |
Description |
|
.spa |
stores document attributes |
|
.spb |
stores blob attributes: strings, MVA, json |
|
.spd |
stores matching document ID lists for each word ID |
|
.sph |
stores index header information |
|
.sphi |
stores histograms of attribute values |
|
.spi |
stores word lists (word IDs and pointers to .spd file) |
|
.spk |
stores kill-lists |
|
.spl |
lock file |
|
.spm |
stores a bitmap of killed documents |
|
.spp |
stores hit (aka posting, aka word occurrence) lists for each word ID |
|
.spt |
stores additional data structures to speed up lookups by document ids |
|
.spe |
stores skip-lists to speed up doc-list filtering |
|
.spds |
stores document texts |
|
.tmp* |
temporary files during index_settings_and_status |
|
.new.sp* |
during indexing new version of the index is written by default in the same folder |
|
.old.sp* |
after rotation previous version files are saved with .old extension |
To verify that the index is built successfully, we will open the MySQL console again, and execute the “show tables;” command. It should show the product index.
Figure 12 – Show tables command result
Also, if we execute a select query over the created index, the data should be visualized as the following:
Figure 13 – Selecting data from index result
Connecting to Manticore search using SQL Server linked server object
As mentioned previously, in building full-text indexes article, to establish a connection with the Manticore search from the SQL Server management studio, we should first install the MySQL ODBC connector from the following link.
To create a linked server, we can use the following query:
|
1 2 3 4 5 6 7 8 |
EXEC master.dbo.sp_addlinkedserver @server=N'MANTICORE', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'Driver={MySQL ODBC 8.0 ANSI Driver}; Server=127.0.0.1;Port=9306,charset=UTF8;User=;Password=;OPTION=3' GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MANTICORE', @useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL GO |
After creating the linked server, we can read data from the Manticore indexes by executing SphinxQL statement using OPENQUERY() option:
|
1 |
SELECT * FROM OPENQUERY(MANTICORE,'SELECT * from product') |
The result should look like the following:
Figure 14 – Reading data from Manticore using a linked server object
Conclusion
In this article, we talked briefly about the Manticore search and how it differs from the Sphinx search engine. We showed how to install this engine and how to build full-text indexes from SQL Server databases. Finally, we explained how we could connect to the Manticore engine from the SQL Server management studio via a linked server object.
At the end of this series, we should be familiar with Sphinx and Manticore search engines, and you should be able to decide which one of these engines you have to use. If you are a new user, it is preferable to go with Manticore since more resources are found online, and it is easier to get supported.
External Links
Table of contents
| Getting started with Sphinx search engine |
| Building full-text indexes using the Sphinx search engine |
| Manticore search: a continuation of the Sphinx search engine |
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023