Efficiency in Database Design - Part 2 Division 3
Introduction This is part 2 of my series Efficiency in Database Design. I assume that you have read all the different parts of the series up to this point. In this part of the series, we look at what is called, Index in database tables. An index makes your database tables to operate fast.
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at That is, contact me for the slightest problem you have about what you are reading.
What is an Index? An index is a DBMS object that can be set on one or a group of columns of a table. It can be set on a single column, such as a single primary key; it can be set on a group of columns, such as a composite primary key. You can have more than one index for a table.
Uses of Index Index can be classified as two types: normal index and unique index. Assume that you have a table already stored in a database. You can make the DBMS display the table data on the screen the way it was recorded. However, this is usually not what you will do. You will usually be displaying your table in a sorted order (alphabetically), based on a column. If the normal index is set on the column on which the sorting will be based, then the table data will be retrieved faster then if the index was not set.
An index has a lot of information about your table. Assume that you want to retrieve only a particular row: You need to identify that row with the table primary key value for that row. If an index has been set on the primary key column, then the row will be retrieved faster than if no index was set.
You can program your DBMS to link your tables in the database (disk) by certain columns (especially keys). You may want to display data from the two tables, based on the linked columns. If these linked columns were index, then the retrieval of the data would be faster than if they are not indexed.
A primary key has to be unique. However, if the user decides to type in two of the same values in the primary key column, what will you do? You can prevent this. To prevent this, set the unique index on primary key column of your table. You can also use the unique index to force any other column to be unique.
You DBMS documentation will tell you how to set index on a column. You can set an index on any column. If you want your database to operate fast, then set indexes on all your tables.
Summary of Index Uses - Maintains uniqueness of columns especially keys. - Speeds data retrieval on indexed columns. - Ordering of rows. - Speeds joining of tables on indexed columns
Disadvantages of Indexing - Uses extra disk space. - Many indexes slow down updating process of the table - For small (short) tables there is no gain in speed.
Advice So, as a database designer which columns should you index? Should you index at all? There is no exact solution for this. Take my advice as follows:
- Define a unique index on a primary key. - Index all foreign keys. - Want to insist on unique values for a column, index that column. - Index frequently searched columns. - Index columns that frequently determine the sort order. - The longer the table, the likelihood that it should have indexes.
That is it for database indexes. We take a break here and continue in the next part of the series.
Chrys
To arrive at any of the parts of this division, type the corresponding title below in the search box of this page and click Search (use menu if available).
Designing Database Tables - Part 13 Designing Database Tables - Part 14 Designing Database Tables - Part 15 Designing Database Tables - Part 16 Designing Database Tables - Part 17 Designing Database Tables - Part 18 Designing Database Tables - Part 19 Designing Database Tables - Part 20 Designing Database Tables - Part 21 Designing Database Tables - Part 22 Designing Database Tables - Part 23 Designing Database Tables - Part 24 Designing Database Tables - Part 25 Designing Database Tables - Part 26 Designing Database Tables - Part 27 Designing Database Tables - Part 28 Designing Database Tables - Part 29 Designing Database Tables - Part 30 Designing Database Tables - Part 31 Designing Database Tables - Part 32 Designing Database Tables - Part 33
iAutoblog the premier autoblogger software
No comments:
Post a Comment