In order to quickly calculate some features for later calculations I have attempted to store a table that allows looking up distances between locations that have been pre-calculated. I am using Python and SQLalchemy. I have 76,807,696 rows in my table. However a single query is taking upwards of 300 seconds.
My Database is defined as:
class LocationsLookupTable(db.Model): """Use this class to create the database Model for the locations lookup table. This class is used for creating the database table Model for the locations lookup table. It will store the ID pair and their distance value. There will also be a unique ID for each value in the database table. """ id = db.Column(db.Integer, primary_key=True, unique=True) firstId = db.Column(db.Integer, nullable=False) secondId = db.Column(db.Integer, nullable=False) firstLocationGeoPointX = db.Column(db.Float) firstLocationGeoPointY = db.Column(db.Float) secondLocationGeoPointX = db.Column(db.Float) secondLocationGeoPointY = db.Column(db.Float) distanceValue = db.Column(db.Integer)
And I am querying it using the following query:
result = models.LocationsLookupTable.query.filter_by( firstId=firstId, secondId=secondId).first()
I have some theories as to why the performance is so bad. The first is that I simply have too many rows. The second is that a database is not suited for this task. The third is that my query is not well formulated. This is however my first attempt at using a Database of any kind so I would like to ask how I can speed up my query before I would make any large changes to my project.
Edit: I solved my problem. @ypercubeᵀᴹ was indeed correct in that the solution was to index my columns that I was querying. SELECT statements are incredibly fast now by a huge margin, taking roughly 0.03 seconds. Thankyou.