Versions Compared

Old Version 3

changes.mady.by.user Unknown User (xiaokang)

Saved on

compared with

New Version Current

changes.mady.by.user Unknown User (xiaokang)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  1. add a new index type INVERTED index
  2. support create INVERTED index with tokenizer and parser and fast fulltext search on text column with type char/varchar/string.
  3. support create INVERTED index without tokenizer parser and fast equal, range operators on text column with type char/varchar/string.
  4. support create INVERTED index without tokenizer parser and fast equal, range operators on numeric column with type int*/float*/date/datetime.

...

Code Block
languagesql
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("tokenizerparser"="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
languagesql
mysql> CREATE INDEX idx_request ON httplogs(request) USING INVERTED PROPERTIES("tokenizer"="english")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
languagesql
-- 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
languagesql
SELECT * FROM httplogs WHERE status = 404;

...

Code Block
languagesql
SELECT COUNT() FROM httplogs WHERE size > 1024;

Implementation

Scheduling


Scheduling

Step1: INVERTED index SQL spec and code 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 type        # DONE
Step3: INVERTED index implementation for numeric type    # DONE
Step4: INVERTED index implementation for array type         # DONE
Step5: ADD/DROP index opt for only write index file but not original segment data file    # TODOspecific implementation steps and approximate scheduling.