Here is a great in-depth article on what happens when you define an index on a SharePoint column. You get to the Indexed Columns functionality by going into the settings of a list or library and looking at the links at the bottom of your column list.
It’s very simple to check a box next to a column you want to index, but be aware, you are NOT creating a true SQL Server index. What you are doing is putting the values of the column in a name/value pair table and SharePoint is using these entries in a join to the UserData table in an attempt to increase performance when you have thousands or millions of items in a list or library.
My experience has shown that, in our situation where we have libraries with up to 40,000 items and custom SharePoint applications querying those libraries, we are not seeing any significant performance increase. Your mileage may vary.