Question: I want to remove an index in a table whose access in php never uses the indexed column. Index takes up extra space and I am trying to trim it. It's a table of phone numbers. A phone number is linked to a user profile's id. So it has 3 columns. `id` (index), `number` and `person`. I was wondering if removing the index will affect the queries that use `number` or `person` in the where clause. My gut feeling is that it shouldn't but I am afraid computer science doesn't work on gut feelings. The data is accessed via `join`s. For example...

```
SELECT *
FROM people ... LEFT JOIN
     phoneNumbers
     ON people.id = phoneNumbers.person

```

Edit: Apparently no one seems to be able to answer the question in the title.

Answer: 
In the case you show, only the `person` column would benefit from an index.

Indexes help in basically four cases:

* **Row restriction**, that is finding the rows by value instead of examining every row in the table.

	+ **Joining** is a subset of row restriction, i.e. each distinct value in the first table looks up matching rows in the second table. Indexing a column that is referenced in the `ON` clause is done in the same way you would index a column referenced in the `WHERE` clause.
* **Sorting**, to retrieve rows in index order instead of having to sort the result set as an additional step.
* **Distinct and Group By**, to scan each distinct value in an index.
* **Covering index**, that is when the query needs only the columns that are found in the index.

In the case of InnoDB, every table is treated as an *index-organized table* based on its primary key, and we should take advantage of this because primary key lookups are very efficient. So if you can redefine a primary key on your `phoneNumbers.person` column (in part), that would be best.
I think it is a good idea for all tables to have explicit primary keys and an index necessarily comes with these. For instance, it becomes difficult to delete rows in the table, if unwanted duplicates were to appear.

In general, indexes are used for `where` clauses, `on` clauses, and `order by`. If you have an `id` column, then foreign key references to the table should be using that column, and not the other two columns. The index might also be used for a `select count(*) from table` query, but I'm not 100% sure if MySQL does this.

If removing an index on a column makes that big a difference, then you should be investigating other ways to make your database more efficient. One method would be using partitioning to store different parts of the database in different files.