Page History

...

Code Block

language	sql

CREATE TABLE httplogs (
  ts datetime,
  clientip varchar(20),
  request string,
  status smallint,
  size int,
  INDEX idx_size (size) USING INVERTED,
  INDEX idx_status (status) USING INVERTED,
  INDEX idx_clientip (clientip) USING INVERTED PROPERTIES("parser"="none"),
) ENGINE=OLAP
DUPLICATE KEY(ts)
DISTRIBUTED BY RANDOM BUCKETS 10
PROPERTIES ("replication_allocation" = "tag.location.default: 1"); -- replication 1 for localhost test


mysql> show index from httplogs;
+---------------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                           | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| default_cluster:testdb.httplogs |            | idx_size     |              | size        |           |             |          |        |      | INVERTED   |         |
| default_cluster:testdb.httplogs |            | idx_status   |              | status      |           |             |          |        |      | INVERTED   |         |
| default_cluster:testdb.httplogs |            | idx_clientip |              | clientip    |           |             |          |        |      | INVERTED   |         |
+---------------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.01 sec)

add an INVERTED index to a table

Code Block

language	sql

mysql> CREATE INDEX idx_request ON httplogs(request) USING INVERTED PROPERTIES("parser"="english");
Query OK, 0 rows affected (0.03 sec)


mysql> show index from httplogs;
+---------------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                           | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| default_cluster:testdb.httplogs |            | idx_size     |              | size        |           |             |          |        |      | INVERTED   |         |
| default_cluster:testdb.httplogs |            | idx_status   |              | status      |           |             |          |        |      | INVERTED   |         |
| default_cluster:testdb.httplogs |            | idx_clientip |              | clientip    |           |             |          |        |      | INVERTED   |         |
| default_cluster:testdb.httplogs |            | idx_request  |              | request     |           |             |          |        |      | INVERTED   |         |
+---------------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)

// Other type of indexes:
CREATE INDEX idx_ngrambf ON tblname(username) USING NGRAM_BF PROPERTIES("size"="3", "other" = "256")  COMMENT "xxx";

CREATE INDEX idx ON tblname(username) USING BITMAP;

CREATE INDEX idx ON tblname(username) USING BLOOM_FILTER PROPERTIES("xxx"="asdasd");

fulltext search query

Code Block

language	sql

-- search for request contains word 'login'
SELECT * FROM httplogs WHERE request MATCH 'login';

-- search for request contains word 'login' or 'error'
SELECT * FROM httplogs WHERE request MATCH 'login error';

-- search for request contains word 'login' and 'error'
SELECT * FROM httplogs WHERE request MATCH_ALL 'login error';

equal query

Code Block

language	sql

SELECT * FROM httplogs WHERE status = 404;

...

Step1: INVERTED index SQL spec and code interface (2022.10)interface # DONE

Step1.1: SQL syntax and metadata

Step1.2: index write interface and related call in write path

Step1.3: index query interface and related call in query path

Step2: INVERTED index implementation for string typetype # DONE

Step3: INVERTED index implementation for numeric typetype # DONE

Step4: INVERTED index implementation for array typetype # DONE

Step5: ADD/DROP index opt for only write index file but not original segment data file # TODO

Page tree

Versions Compared

Old Version 5

New Version Current

Key