Mysql – Performance and correctness of selecting only most recent entries across joined tables

MySQL

I have 2 tables: things and purchases. I am trying to select only the most recent purchase record for each thing.

CREATE TABLE `things` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `product_image` varchar(1024) DEFAULT NULL,
  `barcode` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `unique_barcode` (`barcode`)
) ENGINE=InnoDB AUTO_INCREMENT=643 DEFAULT CHARSET=utf8

CREATE TABLE `purchases` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `thing_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `status` enum('IMMEDIATELY','SOON','LATER','INACTIVE') DEFAULT 'IMMEDIATELY',
  `estimated_number_of_days` int(4) NOT NULL DEFAULT '7',
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `purchase_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `predicted_replace_days` int(4) NOT NULL DEFAULT '7',
  PRIMARY KEY (`id`),
  KEY `foreign_key_things` (`thing_id`),
  KEY `foreign_key_users` (`user_id`),
  CONSTRAINT `foreign_key_things` FOREIGN KEY (`thing_id`) REFERENCES `things` (`id`),
  CONSTRAINT `foreign_key_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8

The following query (based on this answer) appears to work. My questions are:

  1. Is this actually doing what I want it to?
  2. How can I optimize the performance of this query (indexes, etc.)?
  3. Is there a more efficient way to do this in MySQL?

SQL:

SELECT purchases.id AS purchase_id, thing_id, purchase_date, name, predicted_replace_days, product_image, barcode
FROM (
    SELECT id, MAX(purchase_date)
    FROM purchases
    GROUP BY id
    ORDER BY purchase_date DESC
) AS sub
JOIN purchases ON purchases.id = sub.id
INNER JOIN things on thing_id = things.id
WHERE user_id = 1
GROUP BY thing_id;

Thanks!

UPDATE: I think I was overcomplicating it. Here is what looks like a simpler and more correct query:

SELECT purchases.id AS purchase_id, MAX(purchase_date) AS purchase_date, predicted_replace_days, name
FROM purchases
INNER JOIN things ON purchases.thing_id = things.id
GROUP BY thing_id

Best Answer

Something like this:

SELECT purchases.id AS purchase_id, thing_id, purchase_date, name, predicted_replace_days, product_image, barcode
FROM (
    SELECT thing_id, MAX(purchase_date) pd
    FROM purchases
    GROUP BY thing_id   
) AS sub
JOIN purchases ON purchases.id = sub.thing_id and sub.pd=purchase_date
INNER JOIN things on thing_id = things.id
WHERE user_id = 1