What is the difference between a spatial index and an attribute index. What are their purposes?
Spatial Index is used for indexing your geographic data. You can compare it to a Mapbook with a index on page 1, telling what the other pages contain. Assigning a Spatial Index to a layer ensures much better performance than without a Spatial Index - ArcGIS only gets the data you need from the "index pages", that you request.
Attribute Index is assigned to a field in an attribute table and the purpose is also here to enhance the overall performance when querying data.
Hope this simple explaination helps you.
Think of your data as a book. If you have a table with millions of records, you can think of it as a book with millions of pages. If you were to find all instances of a given word inside of this book, it would take a very long time. It is the same when searching a database table with many records.
An attribute index functions the same as an index in a book. If you are looking for a particular record, an index helps the database engine find it faster. When a database creates an index, it creates a separate index table which organizes your key fields in an easy to find manner. The index table contains address pointers to where the record can be found in the source table.
A spatial index is the same idea. It makes finding spatial relationships between geometries faster. If you were looking for what polygons intersected another polygon, it wouldn't be very efficient to scan every single vertex for every single polygon in a spatial table.
A spatial index provides metadata for quickly placing spatial data in relationship to other spatial data. A "first order" spatial query will compare the MBR (minimum-bounding-rectangle) of polygons to see if the extent of the polygons even match. If so, a "second order" query will see if the actual vertices interact.
Hope this helps.