I have to implement a table to frequently query for 'less than' and 'greater than' on 2 columns (xco, yco). For example, consider the following table
I would frequently fire a query like - give me all ids that have xco > 6. Or give me all ids that have yco < 9. xco and yco would change. But not so frequently.
What could be a good database design for such use case? I am expecting the number of rows to be between 10,000 to 50,000. I am currently trying AWS RDS since other parts of the system are on AWS. Would DynamoDB be any good for this case? I have pretty basic knowledge about SQL and almost none about NoSQL.
Any relational database will easily perform such queries.
CREATE TABLE foo ( id ... xco INT ... yco INT ... PRIMARY KEY(id), INDEX(xco), INDEX(yco) )
Then these can easily be done:
SELECT * FROM foo WHERE xco > 6; SELECT * FROM foo WHERE yco < 9;