MySQL - order by in a different order?/Forum (SoCoder)

Forum Home

Web Development

MySQL - order by in a different order?

User

Message

Posted : Thursday, 15 September 2011, 19:19 | Permalink | Mark Here

HoboBen

WW Entries : 9

I almost have this query working, however it gives the timestamps in the wrong order.

However, as I understand it group by has nothing to do with sorting (grouping always happens before ordering).

In this image, I am trying to get a list of all articles, with the only the latest revision entry returned for each article. When two revision share an articleId, they are revisions of the same article.

I am also joining the user table to get the user.name based on the revision.userId/user.id keys (this part is working).

So right now, I am successfully returning only one revision for each articleId. But I am, however, only returning the oldest revision.

How would I amend my query to do the same for the newest revision for each article?

Thanks -- I hope that made sense!

---

Reading around, it seems I will probably have to embed a query inside this query somehow.

-----

github

Posted : Thursday, 15 September 2011, 19:44 | Permalink | Mark Here

HoboBen

WW Entries : 9

I think I have this fixed:

select revision1.*, user.id, user.name from revision revision1, user
where user.id = revision1.userId
and revision1.revisionTime =
(select max(revision2.revisionTime) from revision revision2
where revision2.articleId = revision1.articleId);

+----+-----------+--------+-----------+---------+---------------------+---------------+------------+---------------+-----------+----+-------+
| id | articleId | userId | sectionId | visible | revisionTime        | publishedDate | title      | synopsis      | body      | id | name  |
+----+-----------+--------+-----------+---------+---------------------+---------------+------------+---------------+-----------+----+-------+
|  3 |         1 |      3 |         1 |       1 | 2011-09-16 02:45:01 | 2001-02-03    | Test Title | Test Synopsis | Test Body |  3 | test2 |
|  4 |         2 |      1 |         1 |       1 | 2011-09-16 02:45:05 | 2001-02-03    | Test Title | Test Synopsis | Test Body |  1 | dev0  |
+----+-----------+--------+-----------+---------+---------------------+---------------+------------+---------------+-----------+----+-------+
2 rows in set (0.00 sec)

-->

A bit ugly, considering how easy the first was. However I'm happier not using "group by" as it's a dodgy command.

-----

github

Posted : Friday, 16 September 2011, 11:32 | Permalink | Mark Here

HoboBen

WW Entries : 9

Dunno if anyone will know the answer to this, but is that embedded subquery going to run for every row in the table? ----- github

Posted : Friday, 16 September 2011, 12:08 | Permalink | Mark Here

JL235

I'd be pretty sure it won't run on every row.

Posted : Friday, 16 September 2011, 12:51 | Permalink | Mark Here

HoboBen

WW Entries : 9

Benchmarking, for 10,000 revisions of 50 articles:

select revision1.*, user.id, user.name
from revision revision1, user
where user.id = revision1.userId
and revision1.revisionTime =
(select max(revision2.revisionTime)
from revision revision2
where revision2.articleId = revision1.articleId)
order by revision1.publishedDate desc
51 rows in set (8.04 sec) // can reduce to 6.75 sec by comparing revision.id instead of revision.revisionTime

select revision.*, user.id, user.name from revision, user where revision.userId = user.id group by revision.articleId;
50 rows in set (0.11 sec)

8.04 seconds vs 0.11 seconds... a bit worrying.

Also, I have no idea why but I have two rows for article.id=36 in the first query! Additionally the second (faster) query doesn't actually pick the correct userId.

Bloody databases.

-----

Good news though, it appears the query is cached -- it takes 0.0 seconds if you call it twice in a row! That helps slightly, but it won't always work. Any suggestions on how I might rewrite the query?

Perhaps it's time to denormalise and have a second table.

-----

github

Posted : Friday, 16 September 2011, 13:48 | Permalink | Mark Here

HoboBen

WW Entries : 9

Sod it.

mysql> describe article;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| revisionId | int(10) unsigned | NO   | UNI | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

-->

select revision.* from article, revision where article.revisionId = revision.id;
50 rows in set (0.00 sec)

Slightly more work for PHP to do (and hence more places where the database will have to be locked by PHP to ensure correctness), but it's quicker and simpler than the alternative!

-----

github