[mysql] rijen uit tabel 'matchen'

[Verwijderd]

Ik ben op dit moment bezig met een projectje en daarbij zit ik nu op een punt dat ik even niet weet wat ik het beste kan doen. Eerst even de tabel (simpel weergegeven):
- artikel
- tijd
- lokatie

Wat is nu de bedoeling? Kijken welke artikelen het zelfde traject afleggen. Het zelfde traject afleggen betekend: 2 artikelen moeten elk minimaal 2 keer achter elkaar op dezelfde tijd op dezelfde locatie zijn. Dat betekent namelijk dat er maar 1 mogelijkheid is, dat ze hetzelfde traject tussen die lokaties hebben.

Het probleem is dat de aantallen records waarschijnlijk niet bepaald klein blijven, vandaar dat ik een zo efficient mogelijke oplossing zoek. De eerste oplossing was een query waarin de tabel onder 4 verschillende namen gebruikt werd om de 2 reizen met elk 2 reispunten te matchen. Echter dat was zowiezo onbruikbaar qua performance. (En is het niet zo dat er dan in het geheugen 4 kopieeen gemaakt worden van de tabel? dat wordt dan ook misschien wat veel van het goede)

Daarna heb ik het zo aangepast dat er alleen nog maar gekeken werd naar artikelen op dezelfde lokatie op dezelfde tijd. Om vervolgens met php in een lusje de artikelen eruit te pakken die 2 of meer keer achter elkaar voorkwamen (via order by) Dit is al een flinke verbetering op dit moment maar ik ben er toch niet helemaal tevreden over. Er worden ook nog steeds 2 kopieen van de tabel gebruikt, in hoeverre dat een probleem is weet ik niet.

Hoe kan ik de juiste gegevens efficienter uit de database krijgen, of welke aanpassingen aan de structuur kan ik het beste maken?

[ Voor 3% gewijzigd door Verwijderd op 03-06-2003 13:43 ]

[ACM]

Verwijderd schreef op 03 June 2003 @ 13:41:
Het probleem is dat de aantallen records waarschijnlijk niet bepaald klein blijven, vandaar dat ik een zo efficient mogelijke oplossing zoek. De eerste oplossing was een query waarin de tabel onder 4 verschillende namen gebruikt werd om de 2 reizen met elk 2 reispunten te matchen. Echter dat was zowiezo onbruikbaar qua performance. (En is het niet zo dat er dan in het geheugen 4 kopieeen gemaakt worden van de tabel? dat wordt dan ook misschien wat veel van het goede)

Nee, de tabel zal niet helemaal gekopieerd worden. Tenzij jij alle rijen paarsgewijs aan alle rijen wilt koppelen ofzo
Met het een en ander aan handige indices lijkt het me geen probleem op zo'n manier vergelijkingen in entries in de tabel onderling te maken.

Daarna heb ik het zo aangepast dat er alleen nog maar gekeken werd naar artikelen op dezelfde lokatie op dezelfde tijd. Om vervolgens met php in een lusje de artikelen eruit te pakken die 2 of meer keer achter elkaar voorkwamen (via order by) Dit is al een flinke verbetering op dit moment maar ik ben er toch niet helemaal tevreden over. Er worden ook nog steeds 2 kopieen van de tabel gebruikt, in hoeverre dat een probleem is weet ik niet.

Eventueel kan je die 'lijst met op dezelfde datum' in een temporary table zetten en daar weer een select op uitvoeren.

[Verwijderd]

Ik ben nog eens bezig geweest met de eerste manier omdat dat toch de mooiste oplossing is, maar met weinig succes. Even compleet de tabel:
- tijd
- reis_id (linkt alle reispunten naar een tabel reis met verdere gegevens)
- product_id (linkt ook weer..)
- locatie_id (linkt ook weer..)

Mijn test query:

code:

SELECT een paar velden
FROM locatiepunten l1, locatiepunten l2, locatiepunten l3, locatiepunten l4
WHERE 
l1.product_id = 10 
AND l2.product_id != 10 
AND l1.tijd = l2.tijd 
AND l3.tijd = l4.tijd 
AND l1.reis_id = l3.reis_id 
AND l2.reis_id = l4.reis_id
AND l1.locatie_id = l2.locatie_id
AND l3.locatie_id = l4.locatie_id

Op 1500 testrijtjes duurt die zo'n 0,3 seconden. Het probleem is nu wel dat de query steeds langzamer gaat als hij een paar keer achter elkaar uitgevoerd wordt, maar dat zal wel aan het testservertje liggen hoop ik.

Ik heb nu een index op traject_id (foreign key innodb) en een index op product_id+tijd. Nu snap ik het indexeren niet helemaal uit de manual, zijn deze indexen een verstandige keuze? Ik heb product_id en tijd gepakt omdat dat gegevens zijn die meer verschillen dan de locaties

En dan realiseer ik me zojuist nog iets. Deze query pakt alle mogelijke resultaten, bijvoorbeeld een reis met 3 locaties: 1-2 2-3 1-3 en dit gaat nogal hard natuurlijk bij een reis van bijv. 15 locaties. Ik ga maar eens kijken naar de temporary tabellen...

[ Voor 11% gewijzigd door Verwijderd op 03-06-2003 17:27 ]

[marty]

Ik snap niet precies het idee achter de tabellen - dus waar het voor gebruikt wordt, maar is het niet mogelijk om iedere keer dat je een artikel invoegt, daarbij de route ook al gelijk als string op te slaan. dan moet je na het invoegen van de tijd en locatie alleen het artikel zelf even updaten met de nieuwe route (ouwe, plus nieuw)
Duurt, ietsje langer bij het invoeren, maar je weet in iedergeval zeker dat het eruithalen rete snel gaat (zeker als je er nog een index op plaatst - maar dat vertraagd het invoeren wel weer iets (hoewel je daar bij 1 update query niets van merkt hoor)

[ACM]

Verwijderd schreef op 03 June 2003 @ 16:44:

code:

SELECT een paar velden
FROM locatiepunten l1, locatiepunten l2, 
   locatiepunten l3, locatiepunten l4
WHERE 
l1.product_id = 10 
AND l1.tijd = l2.tijd 
AND l1.locatie_id = l2.locatie_id
AND l2.product_id != 10 
AND l3.tijd = l4.tijd 
AND l1.reis_id = l3.reis_id 
AND l2.reis_id = l4.reis_id
AND l3.locatie_id = l4.locatie_id

Ok, dus je vergelijkt l1 en l2 op basis van het product_id, tijd en locatie_id

Je vergelijkt l3 en l4 op basis van tijd, reis_id en locatie_id.

Je kunt dus sowieso nog wat tijdswinst maken als je een gecombineerde index op product_id, tijd en locatie_id legt. En een gecombineerde index op tijd, reis_id en locatie_id. (wat de beste volgorde van de velden is weet ik zo gauw niet, waarschijnlijk zoals je ze 'aanroept').

Op 1500 testrijtjes duurt die zo'n 0,3 seconden. Het probleem is nu wel dat de query steeds langzamer gaat als hij een paar keer achter elkaar uitgevoerd wordt, maar dat zal wel aan het testservertje liggen hoop ik.

Dit is een query waar mysql gewoon erg slecht in is, dus de kans is aanwezig dat het niet voor verbetering vatbaar is op deze manier.

Ik heb nu een index op traject_id (foreign key innodb) en een index op product_id+tijd. Nu snap ik het indexeren niet helemaal uit de manual, zijn deze indexen een verstandige keuze? Ik heb product_id en tijd gepakt omdat dat gegevens zijn die meer verschillen dan de locaties

Indices zijn er om het zoeken te vermakkelijken, dus als je steeds zoekt op 'die en die met traject_id X' dan is een index op traject_id handig, als het een foreignkey koppeling naar een andere tabel is moet dat sowieso wel voor mysql.

Of de index op product_id+tijd voldoende selectief en bruikbaar is voor je queries kan ik niet zien zo, maar wellicht dat de indices die ik hierboven voorstel nog wat performance winst opleveren.

En dan realiseer ik me zojuist nog iets. Deze query pakt alle mogelijke resultaten, bijvoorbeeld een reis met 3 locaties: 1-2 2-3 1-3 en dit gaat nogal hard natuurlijk bij een reis van bijv. 15 locaties. Ik ga maar eens kijken naar de temporary tabellen...

De kans is aardig groot dat het met temp-tables efficienter op te lossen is

[Verwijderd]

Oke bedankt voor de informatie tot zover, dan is dit een mooi moment om met temp. tabellen aan de gang te gaan. Dat spaart waarschijnlijk ook weer heel wat php-om-het-probleem-heen-bouwen

marty: de route staat niet vast van te voren. En het gaat er nu juist om dat je bijvoorbeeld kan kijken welke artikelen tegelijk van punt a naar b gaan. Waarbij het ook nog zo kan zijn dat a en b bij allebei artikelen slechts een deel van het traject is.