Search TEXT in 1 min

You will learn how to search text in 1 million records int this posting

.
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.644f35d.beta
Copyright 2014 InfiniFlux Corporation 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