[SQL] Databasestructuur voor PB berichten moderniseren

[Flipside22]

Beste GoT'ers,

ik heb een database structuur voor prive berichten. Hier staan een paar miljoen berichten in. Het is vrij simpel:

Table `messages` bevat een paar velden waaronder:
Afzenderid
Ontvangerid
Onderwerp
Bericht
Timestamp

Etc en wat andere velden, de id's wijzen naar een gebruikers tabel.

Op dit moment worden gewoon alle berichten onder elkaar weer gegeven die een gebruiker ontvangt. Het is dus niet 'gebundeld' per afzender. Ik wil dit upgraden (van berichtweergave naar gespreksweergave), en dus met wat aanpassingen / een query een overzicht krijgen van alle gesprekspartners.

Er zijn diverse aanpakken hiervoor, maar wat is de 'best practice'? Zonder structuur aanpassing kom ik er sowieso niet denk ik:

SQL:

SELECT 
`afz`.`username` as `afzusername`,`afz`.`id` as `afzid`,`ontv`.`username` as `ontvusername`,`ontv`.`id` as `ontvid`, MAX(`timestamp`) AS `last` 
FROM `messages`
LEFT JOIN `users` as `afz` ON `from` = `afz`.`id` 
LEFT JOIN `users` as `ontv` ON `to` = `ontv`.`id` 
WHERE `from` = :userid OR `to` = :userid GROUP BY `groupConversationIndex` ORDER BY `last` DESC

Dit is een wat versimpelde versie van de query die ik bedacht had op de huidige structuur. groupConversationIndex is het resultaat van het afzenderid + ontvangerid.
Deze query duurt te lang, ook met het gebruik van de juiste indexes.

Ik heb een andere oplossing in gedachten (dmv 2 extra tabellen), maar weet niet of dit de beste optie is. Hebben jullie suggesties?

[RobIII]

Flipside22 schreef op maandag 5 november 2018 @ 21:40:
Ik heb een andere oplossing in gedachten (dmv 2 extra tabellen)
[...]
Hebben jullie suggesties?

Ja, post die oplossing die je in gedacht hebt eens

[Gomez12]

Hoe presteert je huidige query als je de or eruit haalt en vervangt door een union? En waarom gebruik je left joins naar je users tabel, is een message nog wel geldig als je geen afzender of ontvanger meer hebt? Ik zou zeggen maak daar inner joins van en ik zie niet in waarom het niet zou presteren.

[Flipside22]

Thanks! Met de tips van @Gomez12 kwam ik er al snel achter dat er bij het gebruik van de juiste Index e.a. mis ging. De joins vervangen, en een union van gemaakt en hij presteert nu super!