How to use keys in a table so two columns are unique
If I understand correctly, a given photo will have a maximum of one record in photooftheday, so you could use photo_id as the primary key of that table.  That would ensure uniqueness.

EDIT: Added based on first comment

Basically you will need one more index:

ALTER TABLE `photooftheday`
ADD UNIQUE INDEX `idx_date` (`date`);


That will ensure no two values in the column date will be the same.  As far as optimizing for the query, with the addition of the unique index you will have it covered:  You want indexes on the columns used in joins  (photo_id from each table which, as PKs, are already indexed) and in the WHERE clause (which has only date, which we just indexed).