emotionull emotionull - 2 years ago 102
SQL Question

SQL Table Structure for frequent greater than and less than queries

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

enter image description here

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.

Answer Source

Any relational database will easily perform such queries.

    id ...
    xco INT ...
    yco INT ...
    PRIMARY KEY(id),

Then these can easily be done:

SELECT * FROM foo WHERE xco > 6;
SELECT * FROM foo WHERE yco < 9;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download