3 ms·
Oh okay, so you can store authorization data in a table mapping user_ids to location_ids right? Then instead of using `IN (big_list)` (which can't use indexes)
by SPBS 5y ago
Oh okay, so you can store authorization data in a table mapping user_ids to location_ids right? Then instead of using `IN (big_list)` (which can't use indexes) you join on the mapping table (which can use indexes). Something like
CREATE TABLE locations (
location_id INT PRIMARY KEY,
rating INT
);
CREATE TABLE authorized_locations (
user_id INT REFERENCES users (user_id),
location_id INT REFERENCES locations (location_id)
);
CREATE INDEX authorized_locations_user_id_idx ON authorized_locations (user_id);
CREATE INDEX authorized_locations_location_id_idx ON authorized_locations (location_id);
SELECT AVG(rating) FROM locations JOIN authorized_locations USING (location_id) WHERE user_id = $1;
- winrid 5y agoIn (list) can use an index just fine in most databases. It's "not equal" that has trouble. A better example would be: Product {LocationID, Rating} User{id} UserLocations{UserID, locationID) However, let's say you have an architecture where you have 100s of these "product" tables, since every piece of the app restricts access by location. So not only do you have Product but maybe you have FormResult{LocationID}, Feedback{LocationID} etc. So for a service to find out what a user has access to, first they call the User service to get a list of locations, and then they pass the big list of ids to their own DB, whatever that DB is. I guess this is one case where a monolith and good ol SQL would win. :)