If you use SQL to query data and the number of scanned rows or the size of scanned data in the data table exceeds the upper limit, you can create a secondary index or search index based on the query conditions to improve the query performance.
Problem description
When I use SQL to query data, the following error message appears:
[Message]:The sql scanned rows of main table exceeds the quota, main table rows quota is 100000Possible cause
If you use a non-partition key as a query condition when you use SQL to query data, the data in the entire table is scanned. The number of scanned rows or the size of scanned data may exceed the upper limit.
The upper limit is 100,000 for the number of scanned rows, 128 MB for the size of scanned data, and 30 seconds for the scan duration in a single scan operation performed by executing a SQL statement.
Solution
When you use SQL to query data, make sure that the query conditions include the primary key columns and meet the leftmost matching principle. In addition, make sure that you use the limit parameter in the SQL statement to specify the maximum number of rows that can be returned.
If the query conditions do not include the primary key columns or meet the leftmost matching principle, you can accelerate the query by using one of the following methods. For more information, see Index selection policy.
If you can create a secondary index to include the primary key columns of the secondary index in the query conditions and the query conditions meet the leftmost matching principle, create a secondary index to accelerate the query. For more information, see Overview.
If you specify an aggregation function, such as COUNT, SUM, or AVG, or specify filter conditions based on non-primary key columns in the query conditions, create a search index to accelerate the query. For more information, see Overview.
If you create a search index for the data table and the error persists, check whether the search index includes the fields that are used for the query.