Search Text in 1 Minute

In this tutorial, you will learn how to search text in 1 million records.
Machbase provides 'SEARCH' syntax to search text in VARCHAR and TEXT data type. To use 'SEARCH' syntax, you need to create keyword index in advance.

Step 1 : Create Keyword Index

$ machsql -s localhost -u sys -p manager
=================================================================
     Machbase Client Query Utility
     Release Version 3.5.0.826b8f2.official
     Copyright 2014 Machbase Inc. or its subsidiaries.
     All Rights Reserved.
=================================================================
MACHBASE_CONNECT_MODE=INET, PORT=5656
Type 'help' to display a list of available commands.
Mach> CREATE INDEX eventlog_index ON SAMPLE_TABLE( eventlog) INDEX_TYPE KEYWORD;

Created successfully.
Elapsed time: 0.374

Step 2 : Retrieve data from Machbase with SEARCH syntax

Mach> DESC SAMPLE_TABLE;
[ COLUMN ]
----------------------------------------------------------------
NAME                          TYPE                LENGTH
----------------------------------------------------------------
SRCIP                         ipv4                15
SRCPORT                       integer             11
DSTIP                         ipv4                15
DSTPORT                       integer             11
PROTOCOL                      short               6
EVENTLOG                      varchar             1204
EVENTCODE                     short               6
EVENTSIZE                     long                20
[ INDEX ]
----------------------------------------------------------------
NAME                          TYPE                COLUMN
----------------------------------------------------------------
EVENTLOG_INDEX                KEYWORD_LSM         EVENTLOG

Mach> SELECT EVENTLOG FROM SAMPLE_TABLE WHERE EVENTLOG SEARCH 'view' LIMIT 10;
EVENTLOG                                                                                                                                                              
--------------------------------------------------------------------------------                                                                                       ----
GET /twiki/bin/view/TWiki/ManagingWebs?skin=print HTTP/1.1                                                                                                            
GET /twiki/bin/view/Main/TokyoOffice HTTP/1.1                                                                                                                         
GET /twiki/bin/view/TWiki/ManagingWebs?rev=1.22 HTTP/1.1                                                                                                              
GET /twiki/bin/view/Main/DCCAndPostFix HTTP/1.1                                                                                                                       
GET /twiki/bin/view/TWiki/WebTopicEditTemplate HTTP/1.1                                                                                                               
GET /twiki/bin/view/Main/TokyoOffice HTTP/1.1                                                                                                                         
GET /twiki/bin/view/TWiki/WikiCulture HTTP/1.1                                                                                                                        
GET /twiki/bin/view/Main/MikeMannix HTTP/1.1                                                                                                                          
GET /twiki/bin/view/TWiki/WikiCulture HTTP/1.1                                                                                                                        
GET /twiki/bin/view/TWiki/WikiCulture HTTP/1.1                                                                                                                        
[10] row(s) selected.
Elapsed time: 0.014

Mach> SELECT COUNT(*) FROM SAMPLE_TABLE WHERE EVENTLOG SEARCH 'robots.txt';
COUNT(*)
-----------------------
40283
[1] row(s) selected.
Elapsed time: 0.008

Mach> SELECT SRCIP, COUNT(*) FROM SAMPLE_TABLE WHERE EVENTLOG SEARCH 'robots.txt' GROUP BY SRCIP ORDER BY 2 DESC LIMIT 10;
SRCIP           COUNT(*)
----------------------------------------
81.227.25.139   616
162.80.44.96    596
7.234.88.67     595
227.106.13.91   578
220.192.100.45  570
46.201.48.18    570
231.146.69.51   564
185.22.195.164  564
64.58.31.79     561
50.5.206.126    561
[10] row(s) selected.
Elapsed time: 0.023

results matching ""

    No results matching ""