0

I have two tables. One of them is big, long with more than 500 columns (most of those are tinyint) and 360000 rows. It contains info about photos. Second one is small with just three int columns (surrogate primary key and two foreign keys) and one decimal. It has just 45000 rows and contains info about what people are on those photos. It's so small it doesn't even have indexes. What I have trouble with is selecting photos within clause that have no people marked on them. Query with clause within just first big table takes acceptable few seconds. Then I make something like that

SELECT photos.* FROM photos 
 LEFT OUTER JOIN 
 photos_people ON photos.photo_id = photos_people.photo_id 
WHERE photos_people.photo_people_id is NULL AND ... LIMIT 150;

Now it suddenly takes literally minutes. What am I doing wrong? Is there a way to speed up query?

2
  • Is there a way to speed up: Do not do this: It's so small it doesn't even have indexes. Add an index like ALTER TABLE photos_people ADD INDEX idx_(photo_id, photo_pople_id);
    – Luuk
    Commented Jan 25, 2020 at 13:00
  • Thank you. I really did mistake considering influence of indexes in joined tables. Commented Jan 25, 2020 at 19:19

1 Answer 1

-1

Add an index on the column photo_id on both the tables- photos and photo_people.

1
  • Thanks. It really did the trick. Guess I have to re-read documentation about indexes in joined tables. Commented Jan 25, 2020 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.