Hi,
SELECT , (6371acos(cos(radians(28.549056336607507))*cos(radians(place_lat))*cos(radians(place_long)-radians(77.2689280000161))+
sin(radians(28.549056336607507))*sin(radians(place_lat)))) AS distance,(SELECT t2.user_photo,t2.name FROM users t2 use keys pins.user_id
WHERE t2.user_name= pins.user_name) AS details FROM pins USE INDEX (pin_geo_latlon USING GSI) WHERE user_name NOT IN [‘mustanish123’] AND
status=1 AND cat_id!=1 AND place_lat>=28.546153612815345 AND place_lat<=28.551958980392783 AND place_long>=77.26699680953641 AND
place_long<=77.27085919047727 LIMIT 10 OFFSET 0
Above query takes 40 to 60ms but on using order by it takes more than 120 ms.what should I do to optimize it further.
Above query uses below explain
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan2”,
“index”: “pin_geo_latlon”,
“index_id”: “39255f0ccb6a3882”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “pins”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “28.551958980392783”,
“inclusion”: 3,
“low”: “28.546153612815345”
},
{
“high”: “77.27085919047727”,
“inclusion”: 3,
“low”: “77.26699680953641”
}
]
}
],
“using”: “gsi”
},
{
"#operator": “Fetch”,
“keyspace”: “pins”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((((((not ((pins.user_name) in [“mustanish123”])) and ((pins.status) = 1)) and (not ((pins.cat_id) = 1))) and (28.546153612815345 <= (pins.place_lat))) and ((pins.place_lat) <= 28.551958980392783)) and (77.26699680953641 <= (pins.place_long))) and ((pins.place_long) <= 77.27085919047727))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
},
{
“as”: “distance”,
“expr”: “(6371 * acos((((cos(radians(28.549056336607507)) * cos(radians((pins.place_lat)))) * cos((radians((pins.place_long)) - radians(77.2689280000161)))) + (sin(radians(28.549056336607507)) * sin(radians((pins.place_lat)))))))”
},
{
“as”: “details”,
“expr”: “(select (t2.user_photo), (t2.name) from users as t2 use keys (pins.user_id) where ((t2.user_name) = (pins.user_name)))”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Limit”,
“expr”: “10”
}
]
},
“text”: “SELECT , (6371acos(cos(radians(28.549056336607507))*cos(radians(place_lat))*cos(radians(place_long)-radians(77.2689280000161))+\nsin(radians(28.549056336607507))*sin(radians(place_lat)))) AS distance,(SELECT t2.user_photo,t2.name FROM users t2 use keys pins.user_id \nWHERE t2.user_name= pins.user_name) AS details FROM pins USE INDEX (pin_geo_latlon USING GSI) WHERE user_name NOT IN [‘mustanish123’] AND \nstatus=1 AND cat_id!=1 AND place_lat>=28.546153612815345 AND place_lat<=28.551958980392783 AND place_long>=77.26699680953641 AND \nplace_long<=77.27085919047727 LIMIT 10 OFFSET 0”
}