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 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 rows in a table might be performed faster by a full table scan than by an indexed query.
You should check every query by using the EXPLAIN PLAN statement. Use your real data when doing this because the plan is based 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 by using the following command:
To perform a full table scan, every block in the table is read. For each block, every row that is 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 might be read more than one time. So, if the result set is relatively large, a full table scan might require less I/O than an indexed query.