Slow select query with left join is null and limit results

Question

I have the following query that is being logged as a slow query:

EXPLAIN EXTENDED SELECT *
FROM (
`photo_data`
)
LEFT JOIN `deleted_photos` ON `deleted_photos`.`photo_id` = `photo_data`.`photo_id`
WHERE `deleted_photos`.`photo_id` IS NULL
ORDER BY `upload_date` DESC
LIMIT 50 

Here's the output of explain:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  photo_data  index  NULL  upload_date  8  NULL  142523   
1  SIMPLE  deleted_photos  eq_ref  photo_id  photo_id  767  tbc.photo_data.photo_id  1  Using where; Not exists

I can see that it's having to go through all 142K records to pull the latest 50 out of the database.

I have these two indexes:

UNIQUE KEY `photo_id` (`photo_id`),
KEY `upload_date` (`upload_date`)

I was hoping hat the index key on upload_date would help limit the number rows. Anythoughts on what I can do to speed this up?

Answer 1

You could add a field to your photo_data table which shows whether or not it is deleted instead of having to find out this fact by joining to another table. Then if you add an index on (deleted, upload_date) your query should be very fast.