Query system
A system and method for efficient query processing using a real index of a queried table are described. In one embodiment, the real index is used in an offset query type in order to reduce the number of rows that are sorted and thereby increases efficiency for processing offset query types. In another embodiment, the real index is used in a set operation query type where existing systems utilize a table scan and thereby increases efficiency of set operation query types.
1. A computer-implemented method comprising:
receiving, using one or more processors, an offset query indicating:
a number of rows to be skipped,
a table being queried, the table comprising a plurality of rows;
a number of rows to be fetched from the table being queried;
a first column of the table being queried, wherein the first column is included in a composite index of the table being queried, the composite index including at least one column in addition to the first column; and
a second column of the table being queried;
performing, using the one or more processors, a first sort on the plurality of rows based on first column values using an index scan on the first column of the table being queried, the plurality of rows unsorted on the second column by the first sort;
determining, using the one or more processors, a first column value range within a result of the first sort, the first column value range ranging from a first, first column value to a second, first column value, where the first, first column value is the first column value of a row identified based on the number of rows to be skipped, and where the second, first column value is the first column value of a row identified based on the number of rows to be skipped and the number of rows to be fetched;
identifying, within the result of the first sort, a subset of rows from the table being queried, the subset of rows includes rows having a first column value within the first column value range, and the subset of rows is less than an entirety of the table being queried;
performing, using the one or more processors, a second sort within the subset of rows from the table being queried based on second column values, where the subset of rows has first column values within the first column value range; and
determining, using the one or more processors, based on the offset query, one or more output rows to be fetched from the subset of rows sorted based on their second column values.
2. The computer-implemented method of claim 1 , wherein sorting the subset of rows arranges one or more rows within the subset of rows that have a same first column value in order according to their second column value.
3. The computer-implemented method of claim 1 , wherein the second column is not an index column of the table being queried.
4. The computer-implemented method of claim 1 , wherein the offset query requests that an output be ordered by the first column first.
5. The computer-implemented method of claim 1 , wherein the offset query requests an output that is ordered using an index scan on the at least one column in addition to the first column that is included in the composite index.
6. The computer-implemented method of claim 1 , wherein determining, based on the offset query, the one or more output rows to be fetched comprises:
determining a first output row in the sorted subset of rows based on the number of rows to be skipped; and
using the number of rows to be fetched, determining a number of rows including the first output row to be fetched as the one or more output rows.
7. The computer-implemented method of claim 1 , wherein the offset query uses structured query language (SQL).
8. A system comprising:
one or more processors; and
a memory storing instructions that, when executed by the one or more processors,
cause the system to:
receive an offset query indicating:
a number of rows to be skipped,
a table being queried, the table comprising a plurality of rows;
a number of rows to be fetched from the table being queried;
a first column of the table being queried, wherein the first column is included in a composite index of the table being queried, the composite index including at least one column in addition to the first column; and
a second column of the table being queried;
perform a first sort on the plurality of rows based on first column values using an index scan on the first column of the table being queried, the plurality of rows unsorted on the second column by the first sort;
determine a first column value range within a result of the first sort, the first column value range ranging from a first, first column value to a second, first column value, where the first, first column value is the first column value of a row identified based on the number of rows to be skipped, and where the second, first column value is the first column value of a row identified based on the number of rows to be skipped and the number of rows to be fetched;
identify, within the result of the first sort, a subset of rows from the table being queried, the subset of rows including rows having a first column value within the first column value range, and the subset of rows is less than an entirety of the table being queried;
perform a second sort within the subset of rows from the table being queried based on second column values, where the subset of rows has first column values within the first column value range; and
determine, based on the offset query, one or more output rows to be fetched from the subset of rows sorted based on their second column values.
9. The system of claim 8 , wherein sorting the subset of rows arranges one or more rows within the subset of rows that have a same first column value in order according to their second column value.
10. The system of claim 8 , wherein the second column is not an index column of the table being queried.
11. The system of claim 8 , wherein the offset query requests that an output be ordered by the first column first.
12. The system of claim 8 , wherein the offset query requests an output that is ordered using an index scan on the at least one column in addition to the first column that is included in the composite index.
13. The system of claim 8 , wherein determining, based on the offset query, the one or more output rows to be fetched comprises:
determining a first output row in the sorted subset of rows based on the number of rows to be skipped; and
using the number of rows to be fetched, determining a number of rows including the first output row to be fetched as the one or more output rows.
14. The system of claim 8 , wherein the offset query uses structured query language (SQL).