Question: Im trying to optimse this one for some time now. That is a small extract of a huge query, but that is what the query makes slow at the end. I think you get the simple table structure out of that.
Has someone an idea how to do this elegant and slim?

```
SELECT gref.gid AS gid, COUNT(o.id) AS cnt
FROM objects AS o 
RIGHT JOIN group_xref AS gx ON o.id = gx.oid 
LEFT JOIN group_xref AS gref ON o.id = gref.oid 
LEFT JOIN groups AS groups ON gref.gid = groups.id 
WHERE groups.att = 1 
GROUP BY gref.gid

```

OK here is the EXPLAIN:
<http://htmlpaste.com/ff849935d884a36076f74bd934b0212bac7658e9>

Answer: 
It is very hard to tell what the query is doing without understanding the table structure and the relationships. I wonder if the following query is producing equivalent results and if it runs faster:

```
select gref.gid AS gid, COUNT(*) 
from group_xref gref LEFT JOIN
     groups
     ON gref.gid = groups.id 
WHERE groups.att = 1 
GROUP BY gref.gid;

```

At the very least, I think the double join to `group_xref` is unnecessary. Here is your query without that:

```
SELECT gx.gid AS gid, COUNT(o.id) AS cnt
FROM group_xref gx left join
     objects 0
     ON o.id = gx.oid LEFT JOIN
     groups groups
     ON gx.gid = groups.id 
WHERE groups.att = 1 
GROUP BY gx.gid;

```

If I assume the linkage to objects is 1-many and all objects ids are valid, then this simplifies to:

```
SELECT gx.gid AS gid, COUNT(gx.id) AS cnt
FROM group_xref gx left join
     groups groups
     ON gx.gid = groups.id 
WHERE groups.att = 1 
GROUP BY gx.gid;

```

(which I notice is where I began).
Ensure the following indexes exist:

```
objects.id
group_xref.oid
group_xref.gid
groups.id
groups.att

```

Also, the right join with group\_xref seems redundant, try:

```
SELECT gref.gid AS gid, COUNT(o.id) AS cnt
FROM objects AS o 
LEFT JOIN group_xref AS gref ON o.id = gref.oid 
LEFT JOIN groups AS groups ON gref.gid = groups.id 
WHERE groups.att = 1 
GROUP BY gref.gid

```