If a query cannot use an index, solidDB® must perform a full table scan to execute the query. This involves reading all rows of a table sequentially. Each row is examined to determine whether it meets the criteria of the query’s WHERE clause. Finding a single row with an indexed query can be substantially faster than finding the row with a full table scan. On the other hand, a query that selects more than 15% of a table’s rows may be performed faster by a full table scan than by an indexed query.
You should check every query using the EXPLAIN PLAN statement. Use your real data when doing this because the best plan will depend upon the actual amount of data and the characteristics of that data. The output from the EXPLAIN PLAN statement allows you to detect whether an index is really used and, if necessary, you can redo the query or the index. Full table scans often cause slow response time for SELECT queries as well as excessive disk activity.
To diagnose performance degradation problems, you can request statistics on file operations using ADMIN COMMAND 'perfmon'as described in section Performance counters (perfmon) in the solidDB® Administrator Guide.
To perform a full table scan, every block in the table is read. For each block, every row stored in the block is read. To perform an indexed query, the rows are read in the order in which they appear in the index, regardless of which blocks contain them. If a block contains more than one selected row it may be read more than once. So, there are cases when a full table scan requires less I/O than an indexed query, if the result set is relatively large.