Okay, maybe got something for you: I think you should go for the MAX() over the picture ID. This will be deterministic and give you the latest picture, which probably makes sense in your application.
You should get rid of the ORDER BY. GROUP BY will order for you as well. You can even tell it to do it ASC or DESC.
Then add the recipe datum _and_ the recipe id (in that direction) to the GROUP BY clause. This won't change the semantics as the recipe id is your PRIMARY KEY (and grouping by that would be enough). Add a DESC to _both_ GROUP BY columns.
So you get
GROUP BY recepti.Datum DESC, recepti.ID DESC
at the end of your query.
Be sure to have a UNIQUE KEY (Datum, ID) on recepti and a UNIQUE KEY (IDrecept, ID) on receptislike.
This will allow MySQL to...
* GROUP BY on the UNIQUE KEY on recepti (it's exactly the two same columns in the same order in the KEY and in the GROUP BY, that's why you have to have a DESC on both columns in the GROUP BY, the KEY can't be used if part of it has to be sorted in one and the other part in the other direction)
* use the first part of the KEY on pictures in the JOIN
I tested this on MySQL 5.0.21 with the following query, data and table definitions:
CREATE TABLE `recipes` (
`recipe_id` int(11) NOT NULL,
`dt` date NOT NULL,
PRIMARY KEY (`recipe_id`),
UNIQUE KEY (`dt`,`recipe_id`)
);
CREATE TABLE `pictures` (
`recipe_id` int(11) NOT NULL,
`picture_id` int(11) NOT NULL,
PRIMARY KEY (`picture_id`),
UNIQUE KEY (`recipe_id`,`picture_id`)
);
localhost-test [bvontob]> SELECT * FROM recipes;
+-----------+------------+
| recipe_id | dt |
+-----------+------------+
| 5 | 2006-04-28 |
| 4 | 2006-04-29 |
| 2 | 2006-05-01 |
| 3 | 2006-05-01 |
| 1 | 2006-05-02 |
+-----------+------------+
5 rows in set (0.00 sec)
localhost-test [bvontob]> SELECT * FROM pictures;
+----------+------------+
| recipe_id | picture_id |
+----------+------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 3 | 5 |
| 3 | 6 |
| 5 | 7 |
| 5 | 8 |
+----------+------------+
8 rows in set (0.00 sec)
localhost-test [bvontob]> EXPLAIN SELECT recipe_id, dt, MAX(picture_id) FROM recipes LEFT JOIN pictures USING (recipe_id) GROUP BY dt DESC, recipe_id DESC;
+----+-------------+----------+-------+---------------+-----------+---------+------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+-----------+---------+------------------------+------+-------------+
| 1 | SIMPLE | recipes | index | NULL | dt | 7 | NULL | 5 | Using index |
| 1 | SIMPLE | pictures | ref | recipe_id | recipe_id | 4 | test.recipes.recipe_id | 1 | Using index |
+----+-------------+----------+-------+---------------+-----------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)
localhost-test [bvontob]> SELECT recipe_id, dt, MAX(picture_id) FROM recipes LEFT JOIN pictures USING (recipe_id) GROUP BY dt DESC, recipe_id DESC;
+-----------+------------+-----------------+
| recipe_id | dt | MAX(picture_id) |
+-----------+------------+-----------------+
| 1 | 2006-05-02 | 2 |
| 3 | 2006-05-01 | 6 |
| 2 | 2006-05-01 | 3 |
| 4 | 2006-04-29 | NULL |
| 5 | 2006-04-28 | 8 |
+-----------+------------+-----------------+
5 rows in set (0.04 sec)
I hope this will work on older versions (if you don't yet use MySQL 5.0). You're on your own with adding the rest of the query again, but I think it should work this way.
(Learned something myself while figuring this out - will maybe make a blog post out of this one time.)
Good luck!
Beat
Beat Vontobel
http://www.futhark.ch/mysql