MySQL – How to Use Keys for Unique Columns

MySQL

I want to create a basic database that handles photos. Also, i want to show a photo of the day to the user.
I´ve created a table called ´photo´ and another table ´photooftheday´, like so

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`)
)

CREATE TABLE `photooftheday` (
`photo_id` SMALLINT(5) UNSIGNED NOT NULL,
`date` DATE NOT NULL
)

I didn't add an auto increment column for the table photooftheday, coz I believe it’s not necessary (maybe I’m wrong).

My question is about the table ´photooftheday´. I don’t know how to handle keys so each day contains a photo that is not already selected in another day. In this case, i’m confused about primary keys, unique keys, keys, foreign keys, column indexes…

Also, if it helps, my query to select the photo of the day is going to use the date column, like:

$todays_date = date('Y-m-d');

$q = "SELECT p.photo_id, p.title, p.caption, p.filename
FROM photooftheday pd
JOIN photo p ON pd.photo_id = p.photo_id
WHERE DATE(pd.date) = '$todays_date'";

How do i handle the columns in the table photooftheday, so to optimize it?

Thanks

Best Answer

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.

Related Question