tencent cloud

All product documents
TencentDB for TcaplusDB
DocumentationTencentDB for TcaplusDBTcaplusDB ClientClient Tool Commandsselect with global index
select with global index
Download PDF
Last updated: 2024-12-04 10:12:05
select with global index
Last updated: 2024-12-04 10:12:05
Download PDF

Index-based Query

After the global index feature is enabled, TcaplusDB supports the field query, provided that the field in the query condition must have global index created. The fields in an aggregate query also require global index. An index-based query returns up to 3,000 results.

Supported statements

Query conditions

The following query conditions are supported, including =, >, >=, <, <=, !=, between, in, not in, like, not like, and, or.
Note:
The two values of between are included in the range. For example, if you use between 1 and 100, both 1 and 100 are inclusive. In other words, the query range should be [1,100].
The like query supports fuzzy matching. The wildcard % matches zero or multiple characters, while the wildcard _ matches one character.
tcaplus> select * from pb_generic_index_shardingkey where openid>10 and tconndid<1000;
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|openid|timekey  |tconndid|svrid  |gamesvrid  |other_property                           |items|lockid   |pay|id_uint32|id_int32|
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|201   |"timekey"|201     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|200   |"timekey"|200     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|203   |"timekey"|203     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|204   |"timekey"|204     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|202   |"timekey"|202     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+


total 5 records

tcaplus> select * from pb_generic_index_shardingkey where openid between 1 and 300  and tconndid<1000;
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|openid|timekey  |tconndid|svrid  |gamesvrid  |other_property                           |items|lockid   |pay|id_uint32|id_int32|
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|203   |"timekey"|203     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|204   |"timekey"|204     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|201   |"timekey"|201     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|200   |"timekey"|200     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|202   |"timekey"|202     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+


total 5 records

tcaplus> select * from pb_generic_index_shardingkey where openid>10 or tconndid<1000;
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|openid|timekey  |tconndid|svrid  |gamesvrid  |other_property                           |items|lockid   |pay|id_uint32|id_int32|
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|202   |"timekey"|202     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|203   |"timekey"|203     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|201   |"timekey"|201     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|200   |"timekey"|200     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|204   |"timekey"|204     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+


total 5 records

Paginated query

The paginated query limit offset is supported.
Note:
The paginated query must use limit offset. Neither limit 1 or limit 0,1 can be used.
tcaplus> select * from pb_generic_index_shardingkey where openid>10 limit 3 offset 0;
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|openid|timekey  |tconndid|svrid  |gamesvrid  |other_property                           |items|lockid   |pay|id_uint32|id_int32|
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|200   |"timekey"|200     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|201   |"timekey"|201     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|202   |"timekey"|202     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+

Aggregate query

The following aggregate query functions are supported, including sum, count, max, min, avg.
Note:
The aggregate query does not support limit offset.
Currently, only the count function can be used with distinct. For example, select count(distinct(a)) from table where a > 1000.
tcaplus> select sum(openid), count(*), max(openid), avg(openid) from pb_generic_index_shardingkey where openid>10 ;
1010,5,204,202

Specified field query

The values of specified fields can be queried.
Note:
You can also query nested fields in the Protobuf table. For example, select field1.field2.field3, a, b from table where a > 1000.
tcaplus> select svrid,gamesvrid from pb_generic_index_shardingkey where openid>10 or tconndid<1000;
+------+---------+--------+-------+-----------+
|openid|timekey  |tconndid|svrid  |gamesvrid  |
+------+---------+--------+-------+-----------+
|204   |"timekey"|204     |"svrid"|"gamesvrid"|
+------+---------+--------+-------+-----------+
|203   |"timekey"|203     |"svrid"|"gamesvrid"|
+------+---------+--------+-------+-----------+
|202   |"timekey"|202     |"svrid"|"gamesvrid"|
+------+---------+--------+-------+-----------+
|200   |"timekey"|200     |"svrid"|"gamesvrid"|
+------+---------+--------+-------+-----------+
|201   |"timekey"|201     |"svrid"|"gamesvrid"|
+------+---------+--------+-------+-----------+


total 5 records

Unsupported SQL statements

Using an aggregate query with non-aggregate query

select *, a, b from table where a > 1000;

select sum(a), a, b from table where a  > 1000;

select count(*), * from table where a  > 1000;

Query by order by

select * from table where a > 1000 limit 100 offset 0;

Query by group by

select * from table where a > 1000 group by a;

Query by having

select sum(a) from table where  a > 1000 group by a having sum(a) > 10000;

Multi-table query

select * from table1 where table1.a > 1000 and table1.a = table2.b;

Nested SELECT query

select * from table where a > 1000 and b in (select b from table where b < 5000);

AS query

select sum(a) as sum_a from table where a > 1000;

Other queries not supported

JOIN
UNION
Queries like select a+b from table where a > 1000
Queries like select * from table where a+b > 1000
Queries like select * from table where a >= b
Others
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback

Contact Us

Contact our sales team or business advisors to help your business.

Contact Us
Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 available.

Submit a Ticket
7x24 Phone Support
Hong Kong, China
+852 800 906 020 (Toll Free)
United States
+1 844 606 0804 (Toll Free)
United Kingdom
+44 808 196 4551 (Toll Free)
Canada
+1 888 605 7930 (Toll Free)
Australia
+61 1300 986 386 (Toll Free)
EdgeOne hotline
+852 300 80699
More local hotlines coming soon