Indexes are mostly used to improve the speed of database query, and most preferably applied on the primary key of the table , but however this may not be the case always.
I want to know the decisions that go into applying an index and thus achieving the best results possible.
For not my knowledge for indexes is limited to applying it to the primary key only , in what circumstances would i apply it otherwise
As widely as they are used, an index is best used on any columns that are used often in both a statement's WHERE clause and ORDER BY clause.
Take for instance, the following table:
CREATE TABLE `people` ( `id` int, `name` varchar(255), `phone` varchar(10), `state` varchar(50), `city` varchar(100), `zip` int ); By default, you'll probably only have an index on the id column. Now, let's say you have 20k "people" in your database. When searching by any field other than the id field, you may start to notice a bit of lag. This is where indexes will come in.
There's a good chance that you won't be searching by the phone field (possible, but not likely), so we wouldn't index that one. However, searching by state/city/zip are more likely. When you search by city/state, you often search with them both together with a WHERE clause similar to:
WHERE state = 'Florida' AND city = 'Miami'; Because they're often-used together, you should have an index created with both fields (see multiple-column indexes). On the other hand, when you search by zip, you often do so without a city/state attached, such as:
WHERE zip = 12345; With this, you'll create an index just with the zip field.
Also, it is worth a note that indexes take up not only space, but need to be modified during any INSERT or UPDATE on a table with them. If you have numerous indexes and a lot of records, a single INSERT's time can start to be noticed. So, be sure that you actually need the additional performance boost that the index will give before adding them like salt+pepper.
