MySQL – How to Handle Records Turnover

This depends on table engine?

Yup. MyISAM will lock the table, InnoDB will lock rows

If you're looking for ACIDity, you'll want to be using InnoDB. How InnoDB handles locking is described in this doc page

The following user comments on the above document page illustrate a flaw in InnoDB performance on large tables:

Major gotcha: Rows are locked during updates using indexes. For example, UPDATE tab SET col1=3 WHERE col2=17; will lock the entire table unless col2 is indexed (in which case, only rows where col2=17 will be locked).

and

To expand on the above comment, any operation that does a table scan for update/delete will lock all the rows in the table

Extra curricular reading:

TL;DR: InnoDB index lock is major architectural performance flaw, and that is why you hear that large tables are slower. There’s a big chance that there’re more scalable engines for on-disk writes out there, and all the large InnoDB write/insert benchmarks were severely hit by this.

As mdoyle already mentioned, if you really want every image to show up exactly once over all time, you can add a unique index onto photo_id. And extracted from the comments you also want to assure that every date exists only once in your table.

I would like to mention several other issues.

You might want to use InnoDB as storage engine. This way you can add a foreign key (or foreign index) and make sure that the column photo_id of photooftheday contains only valid values from the column photo_id of photo. The database will check if this is true on inserts and updates. You would also want to put a normal index on photooftheday.date for faster lookups, but since you want a unique index on this column, you can skip the normal index.

The four types of indexes (or keys) are:

• unique key — if you want to restrict values affected by this index to be allowed only once.
• normal index — used for faster lookups. No need to scan the whole data, but use this normal index instead, which is a lot faster.
• foreign key — used to assure that inserted data fits to the data in another (foreign) table.
• primary key — used to uniquely identify every row in the table. This is likely an auto increment column, but it can also be anything else. In your example a row is uniquely identified by either date or date and photo_id (this is based on what you want...). You might add this index too. If you want to put it on date (which seems reasonable from your comments until now), you can skip the unique key I added. The primary key assures that all values are unique, so an additional unique key means only additional overhead.

I have added the storage engine definition, the unique indexes and the foreign key to your create table statements:

CREATE TABLE `photo` (
  `photo_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(100) NOT NULL,
  `caption` VARCHAR(255) NOT NULL,
  `filename` CHAR(29) NOT NULL,
  PRIMARY KEY (`photo_id`)
) engine=innodb;

CREATE TABLE `photooftheday` (
  `photo_id` SMALLINT(5) UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  UNIQUE INDEX (`photo_id`),
  UNIQUE INDEX (`date`),
  FOREIGN KEY (`photo_id`) REFERENCES `photo` (`photo_id`)
) engine=innodb;

Your select statement can be improved too. Firstly MySQL provides date functions too, so there is no need to use the php function and insert values into the select statement. You can use the mysql function NOW(), which returns the current datetime stamp.

The other aspect is a little bit more complicated. You have a WHERE clause to find all rows for today, but you apply a function on date. This makes it impossible for the database to use your newly created index on date. Luckily you do not need to call the function! Your date column is already of the type DATE, so basically contains yyyy-mm-dd. You only need something to compare this value with, which would be the output of NOW(). NOW() returns something like yyyy-mm-dd hh:ii:ss (so time too), but you only need the date part. You can extract this with the DATE() mysql function. Just put the two functions together then. The whole query looks like:

SELECT p.photo_id, p.title, p.caption, p.filename
FROM photooftheday pd
JOIN photo p ON pd.photo_id = p.photo_id
WHERE pd.date = DATE(NOW())

and will be executed a lot faster, than the original version.