What is index in database design? I basically can`t understand what is it and the function of it. Is it same with primary key or some candidate key?
Index
It's always a index on the primary key but it's not the same thing. Indexes makes it much faster to look up data. Imagine the following query:
If there is no index it has to go through all the rows in the table to see what tables are from year 1987. If it's very many rows it's not hard to realize that it can be very slow. If you have an index on the column year, it will be much faster. It will only have to look at the index to see what rows has year 1987. This is much faster look up.
On the downside it will slow down inserts and updates on the index column. Indexes also take up some disk space but it's often worth it.
It's possible to use indexes on more than one column. Good if you often have two columns in the where part.
| Code: |
| SELECT * FROM table WHERE year=1987 |
If there is no index it has to go through all the rows in the table to see what tables are from year 1987. If it's very many rows it's not hard to realize that it can be very slow. If you have an index on the column year, it will be much faster. It will only have to look at the index to see what rows has year 1987. This is much faster look up.
On the downside it will slow down inserts and updates on the index column. Indexes also take up some disk space but it's often worth it.
It's possible to use indexes on more than one column. Good if you often have two columns in the where part.
Adding an index on a field will (usually) allow you to search that data faster.
However, because the index needs to maintained for every record, it will slow down insertions and deletes for all record fields and slow down updates on that particular field.
Essentially an index is a separate ordered list of every item in that field. It's (usually) much quicker to search something which is ordered because you don't have to search every record for equality and ranges of values.
However, because the index needs to maintained for every record, it will slow down insertions and deletes for all record fields and slow down updates on that particular field.
Essentially an index is a separate ordered list of every item in that field. It's (usually) much quicker to search something which is ordered because you don't have to search every record for equality and ranges of values.
 |