SQL INDEX

SQL > Data Definition Language (DDL) > IndexIndexes help us retrieve data from tables quicker. Let's use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need. The same principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster. Therefore, it is often desirable to create indexes on tables. An index can cover one or more columns. The syntax for creating a table index is shown in the CREATE INDEX section. Below we discuss some general strategies when building and using an index: 1. Build index on columns of integer type Integers take less space to store, which means the query will be faster. If the column you want to build an index for is not of type integer, consider creating a surrogate integer key (or simply a surrogate column of type integer) which maps one-to-one to the column you want to build the index for. 2. Keep index as narrow as possible Narrower indexes take less space, require less time to process, which in turn means the query will run faster. 3. Column order is important For indexes covering multiple columns, the order of the columns in the index is important. The best practice is to use the column with the lowest cardinality first, and the column with the highest cardinality last. Recall cardinality means the number of distinct values for that column. So, if SELECT DISTINCT (COLUMN1) FROM TABLE_NAME; returns 5, that means the cardinality for COLUMN1 is 5. 4. Make sure the column you are building an index for is declared NOT NULL This can decrease the size of the index, which in turn will speed up the query. 5. Build an index only when necessary The advantages of using an index do not come without a cost. Indexes take up disk space, and they will cause INSERT, UPDATE, and DELETE operations to go slower, since each time one of these operations is carried out, not only does the database system need to update the values in the table, but it also needs to update the indexes. There are no hard rules on how to build indexes. Deciding what indexes to build is often this is an iterative process over time. SQL > Data Definition Language (DDL) > Create Index StatementAs mentioned in the Index overview page, a table index helps SQL statements run faster. The syntax for creating an index is: CREATE INDEX "index_name" ON "table_name" (column_name); Note that an index can only cover one table. We cannot build an index that covers multiple tables. Let's assume that we have the following table, Table Customer Column Name Data Type First_Name char(50) Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime and we want to create an index on the column Last_Name, we would type in, CREATE INDEX IDX_CUSTOMER_LAST_NAME ON Customer (Last_Name); If we want to create an index on both City and Country, we would type in, CREATE INDEX IDX_CUSTOMER_LOCATION ON Customer (City, Country); A simple index is an index on a single column, while a composite index is an index on two or more columns. In the examples above, IDX_CUSTOMER_LAST_NAME is a simple index because there is only one column, while IDX_CUSTOMER_LOCATION is a composite index because there are two columns. There is no strict rule on how to name an index. The generally accepted method is to place a prefix, such as "IDX_", before an index name to avoid confusion with other database objects. It is also a good idea to provide information on which table and column(s) the index is used on. Please note that the exact syntax for CREATE INDEX may be different for different databases. You should consult with your database reference manual for the precise syntax.