Using spatial index in spatialite query?

Question

I posted this question originally Using nearest neighbor query in SpatiaLite?

Using the answer given I was able to get the query working on some test data. However I am having trouble applying a spatial index (which is a must as I have over 1 million points and I am trying to find the closest line in a layer that has over 300,000 lines). I've tried the query below on a test data set, but it runs slower than the query statement without the spatial index. What am I doing wrong?

UPDATE UgandaBikeTaxis_test SET dist=(SELECT 
 ST_Distance(p.geometry, l.geometry) AND
p.ROWID IN
(SELECT ROWID FROM SpatialIndex WHERE f_table_name= "ugandabiketaxis_test" AND search_frame=l.Geometry)
 FROM osm_filtered_prj_test AS l, ugandabiketaxis_test AS p
 WHERE p.uniqueID = ugandabiketaxis_test.uniqueID
 ORDER BY ST_Distance(p.geometry, l.geometry) LIMIT 1);

• user30184: Can you give a reasonable maximum distance from biketaxi stations to nearest roads? Could it be 100 meters or 1 km?
• kflaw: Yes I definitely could but where do I apply that distance?
• user30184: I would build a search_frame around biketaxe stations by using the BuildCircleMBR function gaia-gis.it/gaia-sins/spatialite-sql-latest.html and radius of the max distance. ST_x and ST_y should be used for getting x and y coordinates for BuildCircleMBR. For easier reading I would put AND p.ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name= "ugandabiketaxis_test" AND search_frame=BuildCircleMBR...) last in the query.
• kflaw: So you mean just replace search_frame=l.Geometry with search_frame=BuildCircleMBR...? I don't quite understand. I am new with these queries.
• user30184: Well, your query looks a bit odd to me but without your data it would take me some time to make test layers to play with and I feel a bit lazy right now. Anyway, you query as it stands now is using the geometries of linestrings (actually envelopes of the geometries) as search_frame. That may lead to no hit at all if linestring is very short or to all too many hits if linestring is very long. So I would first use fixed distance from biketaxi stations as a search_frame for having a better control on what happens.