Query Optimization with Indexing Table

Abstract

Query optimization is the component of a database management system that attempts to determine the most efficient way to execute a query. One of the techniques to optimize the query is to use an indexing table that is the primary mean of speeding up access to the database. The principle of indexing table is to select columns from a database table to create the index. Index table is created. This table contains a column that was selected as the index and ROWID column for storing the address of data. Instead of searching full table, we can use the index to quickly position to the first matching value and save a lot of time for searching. In addition, we present the principle of choosing indexes, for example candidate column, high cardinality, etc. Lastly, we tested the efficiency of the indexing table. There are 2 test examples to compare between index and non-index. The first test example result for using index, disk access time reduced by 95.07% and % CPU cost reduced by 97.96%. The second test example result for using index, disk access time reduced by 81.25% and % CPU cost reduced by 66.99%.