Could someone explain sql indexes to me in plain terms?

Question

Could someone explain sql indexes to me in plain terms and then maybe technical "inner work" terms. Thanks a lot if you can.

Answer 1

Same thing as a card index in a library. You go to the 'A' card drawer to find books or authors that are with 'A' etc. SQL indexes are built using algorithms that translate the "key"
field(s) into an integer and then stored in a file that has the numeric number that the key is translated into a nd the "poistion" in the table where that row/data exists. So instead of searching row by row through a table. I search the particular section of the index to find the same "number" as my search parameters and then go directly to the the row that contains the data I am looking for. Hope this helps.

Answer 2

An index provides a fast and efficient way to find a subset of the rows in a table. Indexes are stored in sorted order, and generally implement a tree-structure. Each key in an index has a "pointer" to it's row in the real table.

Operations like looking up a single record, or traversing all records that match part of the index are very fast and hit very few blocks of memory. The fewer columns in the index and the smaller those columns are, the more keys that will fit into a block making traversal of the index that much faster.

While indexes can be critical to performance, you don't want to create more indexes than you need. Every time you do an insert, delete, or update that affects one of the columns in the index, the index requires work to keep it an ordered, balanced tree.

One really handy technique: If you have a particular query that only needs a small number of small columns from a wide table, you can include all the columns that you need in an index. When your query hits the index, it will find all the data it needs and will not hit the row in the real table (and therefore does not have to read the additional block).

Answer 3

I believe indexes are place holders for locations. I know that you only want to use them on a certain amount of data, otherwise it can actually slow down queries.