The impact of index on performance can be resumed as:
Finding the right mix is critical to your application's performance.
When to index and what column to index are things you need to pay attention to in your design. An index does not always mean faster access, in fact, you will find that indexes will decrease performance in many case if the database uses them. See this example with B*Tree
Database can use an index on a column to count the number of rows in the table only when the column has been declared NOT NULL