MySQL Joins, Group By, and Ordering the Group By Choice

Question

Is it possible to order the GROUP BY chosen results of a MySQL query w/out using a subquery? I'm finding that, with my large dataset, the subquery adds a significant amount of load time to my query.

Here is a similar situation: how to sort order of LEFT JOIN in SQL query?

This is my code that works, but it takes way too long to load:

SELECT tags.contact_id, n.last
FROM tags
LEFT JOIN ( SELECT * FROM names ORDER BY timestamp DESC ) n
ON (n.contact_id=tags.contact_id)
WHERE tags.tag='$tag'
GROUP BY tags.contact_id
ORDER BY n.last ASC;

I can get a fast result doing a simple join w/ a table name, but the "group by" command gives me the first row of the joined table, not the last row.

1.) is there an index in the field used to order? 2.) If you order in the opposite direction of an index, it'll be slower, so choose carefully if the index goes asc or desc on each case — Alfabravo, Feb 08 '12 at 19:46

score 1 · Accepted Answer · answered Feb 08 '12 at 20:38

1

I'm not really sure what you're trying to do. Here are some of the problems with your query:

selecting n.last, although it is neither in the group by clause, nor an aggregate value. Although MySQL allows this, it's really not a good idea to take advantage of.
needlessly sorting a table before joining, instead of just joining
the subquery isn't really doing anything

I would suggest carefully writing down the desired query results, i.e. "I want the contact id and latest date for each tag" or something similar. It's possible that will lead to a natural, easy-to-write and semantically correct query that is also more efficient than what you showed in the OP.

To answer the question "is it possible to order a GROUP BY query": yes, it's quite easy, and here's an example:

select a, b, sum(c) as `c sum`
from <table_name>
group by a,b
order by `c sum`

answered Feb 08 '12 at 20:38

Matt Fenwick

48,199
22
128
192

When I do a "group by", it will collapse many rows into one. When it collapses the rows, it will keep info from one of those rows -- in my case, the default row it will keep is not the one I want. – swt83 Feb 08 '12 at 21:00
2

@swt83 You've misunderstood how `group by` works -- **you are not using it correctly, which will lead to unpredictable and frustrating results!** Please read up on it, and aggregate functions such as `max` and `min`. – Matt Fenwick Feb 08 '12 at 21:03
This is my first experience w/ joins and such -- so I'm trying to learn. On further reading you are right, I am misusing this. Thanks. – swt83 Feb 09 '12 at 04:04

score 0 · Answer 2 · answered Feb 08 '12 at 22:20

You are doing a LEFT JOIN on contact ID which implies you want all tag contacts REGARDLESS of finding a match in the names table. Is that really the case, or will the tags table ALWAYS have a "Names" contact ID record. Additionally, your column "n.Last". Is this the person's last name, or last time something done (which I would assume is actually the timestamp)...

So, that being said, I would just do a simple direct join

SELECT DISTINCT
      t.contact_id, 
      n.last 
   FROM 
      tags t
         JOIN names n
            ON t.contact_id = n.contact_id 
   WHERE 
      t.tag = '$tag' 
   ORDER BY 
      n.last ASC

MySQL Joins, Group By, and Ordering the Group By Choice

2 Answers2