mysql 多表 group,使用“GROUP BY”连接多个表和MySQL的最新日期

i have 2 mysql tables

table 1 is "qualitaet-inventar" and has 3000 rows with "sachnummer" as AUTO_INCREMENT

table 2 is "qualitaet" and has a couple of rows.. here is "id" set to AUTO_INCREMENT

the important thing is that "sachnummer" can be exist many times. (see the red marks in picture)

what i want to do is a left join from "qualitaet" to "qualitaet-inventar"

i want to group all "sachnummer" by showing the latest date of "created" on "qualitaet"

and when a "sachnummer" isn't available in "qualitaet" do an empty join to "qualitaet-inventar"

so i have this code

SELECT

i.`sachnummer` AS id,

MAX(q.`created`) AS letztemessung,

i.`sachnummer-name` AS sachnummer

FROM

`qualitaet-inventar` i

LEFT JOIN

`qualitaet` q on i.`sachnummer` = q.`sachnummer`

GROUP BY

sachnummer

ORDER BY

sachnummer ASC

the problem is that it needs a long time to get this result

with this code it works faster

SELECT

q.`sachnummer` AS id,

MAX(q.`created`) AS letztemessung,

i.`sachnummer-name` AS sachnummer

FROM

qualitaet q

LEFT JOIN

`qualitaet-inventar` i on q.`sachnummer` = i.`sachnummer`

GROUP BY

sachnummer

ORDER BY

sachnummer ASC

but i don't get the hole inventory and an empty date with this code

is it possible to get this table in a faster way for high table-rows? :D

edit:

here are my indexes

解决方案

The following shouldn't work any faster than your existing query - but from your description, may do:

SELECT i.`sachnummer` AS id,

MAX(q.`created`) AS letztemessung,

i.`sachnummer-name` AS sachnummer

FROM `qualitaet-inventar` i

JOIN `qualitaet` q on i.`sachnummer` = q.`sachnummer`

GROUP BY sachnummer

UNION ALL

SELECT i.`sachnummer` AS id,

q.`created` AS letztemessung,

i.`sachnummer-name` AS sachnummer

FROM `qualitaet-inventar` i

LEFT JOIN `qualitaet` q on i.`sachnummer` = q.`sachnummer`

WHERE q.`sachnummer` IS NULL

ORDER BY sachnummer ASC

Another possibility:

select id, max(letztemessung) as letztemessung, max(sachnummer) as sachnummer

from

(SELECT `sachnummer` AS id,

`created` AS letztemessung,

'' AS sachnummer

FROM `qualitaet` q

UNION ALL

SELECT `sachnummer` AS id,

cast(null as datetime) AS letztemessung,

`sachnummer-name` AS sachnummer

FROM `qualitaet-inventar` i

) sq

group by id

ORDER BY sachnummer ASC