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:
- Is this actually doing what I want it to?
- How can I optimize the performance of this query (indexes, etc.)?
- 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: