When you write a query in spatialite you have to manually join your query to the spatial index for that index to be used to speed up the query. When QGIS displays data does it take advantage of any spatial indexes as you pan and zoom around your data?
Yes. It does as it appears from looking at the source code for the Spatialite Data Provider.
The QgsSpatiaLiteFeatureIterator class is the one that supplies the features to the map upon sending a rectangle extent. You can just search for 'spatialIndex' in that class to see they actually use the index if available.