N-Gram and Full-Text Search Algorithm Testing for Pattern Recognition in a Chatbot Engine ^†

Abstract

The development of chatbots to access database services and information systems has triggered a lot of research on frameworks for service development, including the development of ISONER (Information System On Internet Messenger). This framework consists of multiple phases including pattern recognition, query processing, and response generation. In its implementation, the framework develops pattern recognition services that are currently based on Natural Language Processing (NLP). Improved pattern recognition algorithms enhance the system’s ability to accurately interpret user intent. The pattern recognition used in this research utilizes built-in plugins from MySQL, namely N-gram and Full-Text Search, which can be run directly on the MySQL engine to reduce latency and do not require another programming language. The FTS and fourgram algorithms gave the best results when applied on 100 test data points, with a threshold of 0.91, accuracy of 91%, precision of 99%, and recall of 92%; the average computation time was 19 s for 100 test data points and 2 min 49 s for 1000 data points tested simultaneously.

1. Introduction

Information is valuable and essential for decision-making, so it requires a fast processing and distribution process. One technology that can provide information easily, quickly, and accurately is a chatbot. Creating a chatbot as a new channel to access information system services provides an alternative solution. In terms of securing a data transmission medium, companies do not need to build a new IM (Internet/Instant Messenger). It is enough to utilize existing IMs, such as Telegram. Users do not need to install special applications, so this process is more practical and can save cellphone memory.

Information System on Internet Messenger (ISONER) is a framework that can be used to build access services to information systems, ranging from business rule conversion, access rights, and validation to interaction models using natural human language [1]. The ISONER framework can reduce expenses, such as those from domain rental, hosting rental, web servers, and internet connection. This is possible because IM services can be used for free, including bot services and their integration into an information system. This concept allows services that were initially only LAN-based to be converted into online chatbot services using the internet.

MySQL is a trusted and reliable open-source database used today. In this study, we used MySQL Community Server 8.0.28 as the database engine. Many of the world’s most popular websites are built using MySQL because it exists on many platforms, and MySQL is famous for its performance, reliability, and ease of use [2]. In addition, MySQL can implement business processes using PL/SQL features, which shortens latency and does not require additional applications. N-gram and Full-Text Search (FTS) are two algorithms that are available as plugins in MySQL and have the potential to be developed into pattern recognition services that can be run on the MySQL engine. Therefore, MySQL has the potential to be applied not only as a database but also as a message-processing engine in chatbot systems.

This research designed and tested the N-gram and FTS algorithms for pattern recognition with various combinations to obtain the best alternative in terms of accuracy, precision, recall, and computation time values for Indonesian chatbot services. The research results are expected to provide alternative solutions for selecting pattern recognition algorithms that can be applied to various existing frameworks, including ISONER.

2. Related Work

In 2018, Sukarsa conducted research that discussed a framework design that could be run on Instant Messaging (IM) technology to access information system services, called Information System on Internet Messenger (ISONER). This framework used Natural Language Processing (NLP) for the pattern recognition process [1]. Further research on the ISONER framework was carried out again by Sukarsa in 2019, proposing an architecture for managing Role-Based Access Control (RBAC) and Single Sign-On (SSO) to complement the information system service functions in the ISONER framework [3]. To further optimize data access speed, NoSQL-based database management scenarios need to be explored [4].

Several studies on pattern recognition in chatbot machines have been conducted with various algorithms. Using knowledge from the database and N-grams as a pattern-matching method, a study was conducted by Setiadi in 2016 [5] and Sukarsa in 2020 [6]. In 2020, Haristiani developed a chatbot integrated with LINE social media using the PHP programming language based on Code Igniter (CI) and the MySQL database for Japanese language learning [7]. Research conducted in [8,9] utilized Natural Language Processing (NLP) to build a chatbot as an information service that had the ability to interact with many individuals to facilitate business activists and accelerate the process of exchanging product information with customers [10].

In 2019, the Full-Text Search with Boolean Mode algorithm was used to speed up the search for information according to the user’s wishes via the chatbot [11,12]. Then, further research was conducted by Mesut, which focused on methods to improve the performance of Full-Text Search on the MongoDB engine. The research showed that indexing the database structure could significantly improve the speed of Full-Text Search [13].

N-gram is a MySQL plugin that can parse words into several characters. It is one of the algorithms that has been test for pattern recognition in ISONER. In 2023, Arifin researched the design of a chatbot using the N-gram method, which functioned as an information service [14]. Anbananthen also researched the application of N-gram in chatbots to identify and correct typographical errors, and it was proven that N-gram could produce good accuracy [15]. In the same year, Ghude applied the N-gram model to Hindi with an experimental approach, and using the N-gram model resulted in quite high accuracy [16]. In addition, N-gram could also be used to perform text classification using feature selection methods [17].

Many studies have discussed the utilization of Full-Text Search and N-gram algorithms. However, no one from the previous studies had implemented a combination of the two algorithms and applied them directly at the MySQL machine level. This framework would not require additional programming languages to implement the concept of pattern recognition and business process services, and applying the algorithms to PL SQL MySQL programming would reduce latency because the whole process would occurr on the same machine.

3. Literature Review

3.1. Core Technologies

Artificial intelligence systems think and behave rationally like humans, receiving input and producing decisions [18]. Chatbots are computer programs designed to converse intelligently with users based on predefined knowledge [19]. The ISONER (Information System on Internet Messenger) framework builds information system access services using natural language interaction, reducing implementation costs by utilizing existing IM platforms [1]. Telegram bots are programmed robots that enable system–human interaction through the Telegram API platform [20,21].

3.2. Web Technologies

Webhook is an HTTP callback API concept that directs information to callback URLs when events occur, enabling real-time chatbot message handling [22,23]. PHP is an open-source server-side programming language for dynamic web development [24,25,26]. SQL is a standard language for managing relational database data through Data Definition Language (DDL) and Data Manipulation Language (DML) commands [26].

3.3. Database and Language Processing

PL/SQL is a procedural programming language extension for MySQL that includes functions, stored procedures, triggers, and events [27]. MySQL is a free, multithreaded, multi-user relational database management system [28,29]. Natural Language Processing (NLP) facilitates human–machine interaction using natural language through text preprocessing techniques including punctuation removal, case folding, tokenization, and filtering [30,31].

3.4. Research Foundation

The calculation foundations used in this research include TF-IDF for term weighting and score calculation, N-gram for token segmentation and similarity measurement, a confusion matrix for classification analysis, accuracy and precision for measuring result accuracy, recall for document retrieval measurement, computation time for system performance evaluation, and ROC for classifier performance evaluation.

4. Design System

4.1. System Overview

An overview of the pattern recognition model’s development can be seen in Figure 1.

Figure 1 shows the chatbot system using Telegram as the User Interface. The PHP inbox engine retrieves webhook messages in real-time and stores them in a tb_inbox table. Messages automatically trigger stored procedures for pattern recognition to identify desired services using Full-Text Search on menu tables with N-gram and Full-Text Index.

The pattern recognition process executes Full-Text Search first, then N-gram, with text preprocessing applied beforehand. Results are shown as menu lists, sorted by match scores between the user’s messages and the menu data. Service processing asks questions based on selected menus, requiring user option selections. User answers are stored in company databases, while pattern recognition results are stored in a tb_outbox table. The PHP outbox engine retrieves tb_outbox data in real time to send reply messages to users.

4.2. Pattern Recognition Block

The pattern recognition process block of the message sent by the user in the chatbot system is shown in Figure 2.

Figure 2 shows the message flow in the pattern recognition block. Messages sent by users through Telegram are stored in the chatbot database. The message is checked to see whether it is in an active conversation. If the user is not conversing, the message continues to the pattern recognition block to perform the pattern recognition process. However, if the user is still in the conversation, the message will proceed to the service-processing block, where the reply message is saved directly to the chatbot database and the service results are saved in the company database. The reply message stored in the chatbot database is sent through the Telegram user.

4.3. Physical Data Model

The database design used was MySQL as an RDBMS. A Physical Data Model (PDM) represented the data structure, where entities were represented as tables with specific data types and foreign key relationships. Tables related to the pattern recognition process were named tb_inbox, tb_outbox, tb_value_history, tb_temp_menu, and tb_setting. The other tables were developed in the ISONER framework [1,3].

5. Implementation

5.1. Chatbot Interface

Figure 3 shows the chatbot interface when one of the user conditions, i.e., sending a message in the form of “/start”, is met.

The chatbot interface shown in Figure 3 is accessed through the Telegram application. The condition occurred when the user sent the first message, which generated a reply from the chatbot in the form of a response to display the main menu.

5.2. Testing Accuracy, Precision, Recall, and Computation Time

Several tests were carried out to find the best combination, including FTS testing with bigram, trigram, fourgram, and fivegram tokens, and only FTS. The comparison results of confusion matrix values from testing 100 test data points between Full-Text Search (FTS) with bigram, trigram, fourgram, and fivegram tokens can be seen in

Table 1. Comparison of confusion matrix values.

	FTS and Bigram	FTS and Trigram	FTS and Fourgram	FTS and Fivegram	FTS
TP	46	78	87	83	84
FP	1	1	1	1	1
FN	49	17	8	12	11
TN	4	4	4	4	4

.

The comparison graph of the accuracy, precision, and recall percentage values based on the confusion matrix results can be seen in Figure 4.

Based on the comparison graph of the accuracy, precision, and recall percentage values, the best results are obtained between Full-Text Search (FTS) and fourgram with an accuracy value of 91%, a precision value of 99%, and a recall value of 92%. Based on the results of the percentage value of accuracy, precision, and recall in testing, the performance of the combination of Full-Text Search (FTS) and fourgram can be described using a Receiver Operator Characteristic (ROC) curve, which can be seen in Figure 5.

Based on the ROC curve, the Area Under Curve (AUC) value is 0.85, and the best threshold value is 2.08 using 100 test data points. Another test was conducted, namely a test to determine the computation time of the chatbot system ten times with a total of one thousand test data points using the Full-Text Search and N-gram methods on MySQL, as can be seen in Figure 6.

The computation time test calculation started from when the message entered the tb_inbox table to when it entered the tb_outbox table. Figure 6 shows the time required to compute each amount of test data, and the fastest time for testing between one hundred and one thousand data points is 2 s. Based on the computation time test results, the average computation time is presented in

Table 2. Average computation time test results.

Total Data	Average Time
100	00:00:19
200	00:00:35
300	00:00:52
400	00:01:09
500	00:01:25
600	00:01:42
700	00:01:59
800	00:02:16
900	00:02:30
1000	00:02:49

.

As shown in Table 2, the average computation time increases with the increase in total test data. Testing with 100 data points results in an average time of 19 s, while testing with 1000 data points results in an average time of 2 min 49 s. Improvements are still needed in the algorithm so that this model can be applied to support the implementation of various real-time systems, such as the use of a bus tracking notification system [32].

5.3. Chatbot Misresponse on Pattern Recognition

Some of the response messages received by users indicated response errors. Some factors causing response errors and their solutions can be seen in

Table 3. Causes of response error.

No.	Causes	Solution
1	There are different meanings between the parameter and the searched menu. Example: “Tambah departemen” with “Buat Departemen”.	Addition of a semantic matching-based algorithm. Adapt the new word by storing it in the knowledge table.
2	Greater number of unmatched words in search. Example: “Lihat Daftar Departemen Perusahaan” with “Lihat Departemen”	Addition of a search feature that automatically lowers the threshold when the previous threshold is not found.
3	Errors in typing when abbreviating words. Example: “Lht Departemen”	Addition of a word repair function.

.

6. Conclusions

Full-Text Search and N-gram algorithms could be applied in the chatbot’s pattern recognition process by using stored procedures, functions, and events directly on the MySQL engine. Testing 100 test data points using Full-Text Search and N-gram with fourgram tokens gave the best results with a threshold of 0.91, accuracy of 91%, precision of 99%, and recall of 92%. Computation time tests of 100 to 1000 test data points obtained an average time of 19 s for 100 test data points and 2 min 49 s for 1000 test data points.

This research still needs to be improved by adding word repair functions and data dictionary tables, adding semantic matching algorithms to minimize data search errors, and developing multiple databases to share the computational burden at the database level for large data.