Indexing schema columns?

General
1

I’m working with a location based message posting system. So I have a table similar to:
float lat
float lon
string messageBody
string parentObjectId

Is there some way to make my table’s lat and lon indexed for faster searching?

Is it the case a table’s objectId is cluster indexed for searching?

Thanks

2

We do not expose indexing capabilities yet. How many records do you plan to have in your table?

Yes, objectId is indexed.

Regards,
Mark

3

Possibly many random queries of 1km by 1km square area of US urban cities.

Currently I use Backendless to query for rows with lon>=lon0 && lon<lon1 && lat>=lat0 && lat <lat1. I’m worried this would be quite slow as the rows increase later on.

And because I treat a row having parentObjectId=null as an original post, and parentObjectId!=null as a response to an original post, I may impose an additional parentObjectId == null to search for original posts.

Just trying to find out best practices I can do now to grow alongside you guys.

Thanks

4

How about using the geo service for that? You can search by radius or within an area (which is what you described). The relationship with parent could be done via geo point metadata. Have you considered that approach?

5

I know with 1 dimensional data you can index a column. lat/lon being 2 dimensional how does that impact searching points within a rectangle in the geo service?

I think you are suggesting I embed a parentObjectId=someObjectId meta at each geo point and perform query on my posting schema where parentObjectId=someObjectId. Though the parentObjectId column isn’t indexed currently perhaps it will be later on?

Thanks

6

We have optimized geo searches to the scientific level )) They should be very fast. There are optimizations we added on the server-side with some additional indexing in the geo store. If the approach I suggested seems reasonable, give it a try.

We’re looking into ways to allow developers determine what columns should be indexed. It is not available yet, but it is on our roadmap.