Temporary table voor zoekfunctie
Het gaat erom dat de gebruiker eigen gegevens in de database heeft staan en die moeten gematcht worden met gegevens die door het systeem al zijn gedefinieerd. Het gaat overigens om vliegtuig registraties, om het misschien iets beter begrijpbaar te maken. Omdat de gebruiker zijn eigen fomaat kan hanteren kan het zijn dat bepaalde gegevens, ook al zouden ze gelijk moeten zijn, dat toch niet zijn. Voorbeeld: Een Belgische F-16 staat in het systeem als FA-01, maar de gebruiker kan ook FA 01, FA01 of FA-1 invullen, dit moet allemaal tot een match leiden met FA-01. Om dat te doen is er een extra tabel met een 'opgeschoonde' waarde voor de systeem gegevens. Daar zal deze registratie als FA1 instaan wat is bepaald via een regex en andere regels.
Het probleem is nu dat ik de gebruiker laat zoeken op zijn eigen data in de database, maar voor de zoektocht start zal ik eerst van alle door de gebruiker ingevoerde gegevens de opgeschoonde waarde moeten bepalen. Die staat niet in de database, die bepaal ik dus op het moment dat het nodig is. Waarop ik dan zoek is het ID en de zojuist bepaalde opgeschoonde waarde. Zoek ik op 1 record dan is dat geen probleem (versimpeld):
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT DISTINCT aa.plane_id, aa.registration
FROM (
(SELECT a.plane_id, m.registration,
FROM planes a
INNER JOIN planes m ON (
(m.registration = a.registration OR m.registration = 'FA1')
AND m.plane_id <> a.plane_id
)
WHERE (m.country_id = a.country_id OR a.country_id = 0)
AND a.plane_id = 1)
UNION
(SELECT a.plane_id, mp.registration,
FROM planes a
INNER JOIN registration_search ms ON (
(ms.search_value = a.registration OR ms.search_value = 'FA1')
AND ms.plane_id <> a.plane_id
)
INNER JOIN planes mp ON ms.plane_id = mp.plane_id
WHERE (mp.country_id = a.country_id OR a.country_id = 0)
AND a.plane_id = 1)
) aa
ORDER BY aa.plane_id, aa.registration;
FROM (
(SELECT a.plane_id, m.registration,
FROM planes a
INNER JOIN planes m ON (
(m.registration = a.registration OR m.registration = 'FA1')
AND m.plane_id <> a.plane_id
)
WHERE (m.country_id = a.country_id OR a.country_id = 0)
AND a.plane_id = 1)
UNION
(SELECT a.plane_id, mp.registration,
FROM planes a
INNER JOIN registration_search ms ON (
(ms.search_value = a.registration OR ms.search_value = 'FA1')
AND ms.plane_id <> a.plane_id
)
INNER JOIN planes mp ON ms.plane_id = mp.plane_id
WHERE (mp.country_id = a.country_id OR a.country_id = 0)
AND a.plane_id = 1)
) aa
ORDER BY aa.plane_id, aa.registration;
De waarde 'FA1' is dus de opgeschoonde waarde, en in dit voorbeeld hoort dat bij het record met id 1.
Wil ik nu echter op id 1 en id 2 zoeken dan kan dat niet meer op deze manier, tenzij ik nog twee SELECTs maak via de UNION. Ik wil echter op 100 records tegelijk kunnen zoeken, wat tot 200 SELECTs zou leiden....
Vraag is voor mij, wat kan wel. Een temporary table komt in me op, omdat ik dan alle berekende waardes in een tabel heb staan en dan werkt het vrij eenvoudig via joins. Dan kan ik op zoveel records zoeken als ik wil. Mijn kennis van temporary tables is echter beperkt en ik weet dus niet wat daar de voorwaarden en nadelen van zijn. Hoe zit het bijvoorbeeld met naamgeving (om te voorkomen dat er twee users tegelijk zoeken en elkaar in de weg zitten), hoe zit het met performance, etc. Iemand die daar iets op kan zeggen, bij voorkeur in combinatie met het voorbeeld? Eventuele andere oplossingen zijn overigens ook zeer welkom!
Gewijzigd op 12/05/2014 19:04:22 door Erwin H
Niet met WHERE a.plane_id = 1 inderdaad, maar wel met WHERE a.plane_id IN (...). Is dat geen oplossing, eventueel met een subquery?
Naamgeving is niet het probleem, dat werkt per connectie (wordt intern bijgehouden).
Voordeel is dat het (buiten het temporary) normale tabellen zijn.
>> Probleem is echter dat de gegevens waar ik op wil zoeken niet allemaal in de database staan
Jou kennende zal je daar wel een reden voor hebben, dus vraag ik hem maar ;-)
Gewijzigd op 12/05/2014 17:33:08 door Ger van Steenderen
Er is inderdaad een reden voor :-)
Bij de systeemgegevens staat het in de database omdat er regelmatig op gezocht wordt. Bij de gebruikers is dat niet zo. Alleen als er een record wordt toegevoegd is het nodig, maar dan maar eenmalig en verder niet in de situatie als hierboven waardoor het als losse parameter kan worden gebruikt. Het enige andere moment waarop het nodig kan zijn is als de besproken zoek functie gebruikt wordt. In principe zal dat maar 1 keer zijn per record, in de meeste gevallen zelfs nooit. Als bij invoer de juiste link namelijk al is gelegd dan zal er nooit meer op gezocht hoeven te worden.
Het opslaan van deze opgeschoonde data is dus nutteloos, behoudens die ene mogelijke keer dat er op gezocht moet worden.
Toevoeging op 12/05/2014 18:43:24:
Handig.... ik heb de temporary table nodig in beide selects van de UNION. En dat vindt MySQL niet leuk "Can't reopen table...". Het blijkt dat MySQL niet in staat is om meer dan 1 keer in dezelfde query naar een temporary table te verwijzen. Dus nu heb ik al twee temporary tables nodig.....
Bug hier: http://bugs.mysql.com/bug.php?id=10327
Ik kan die self-join in de eerste select ook niet helemaal plaatsen.
De self join is nodig omdat ik gegevens uit de tabel aan het vergelijken ben met gegevens in andere rijen uit dezelfde tabel. Ik wil dus weten of er rijen zijn waarvoor de registratie gelijk is aan de registratie van de rij die gebruiker heeft opgegeven, of waarvan de registratie gelijk is aan de opgeschoonde versie van de registratie die de gebruiker ooit heeft ingevoerd. In de join filter ik daarbij al de rij zelf, want in die rij ben ik niet geinteresseerd.
(er stond alleen nog een foute alias ergens, die is verbeterd)
- plane_id staat voor bv een F-16 en is niet unique in planes?
- in planes.registration kunnen dan ook FA-1, FA01 etc. voorkomen?
- in de tabel registration_search staan dan de afwijkende waardes met de officiele waarde?
- plane_id is de unieke sleutel van de tabel en staat dus niet voor het type.
- in registraation kan inderdaad vanalles voorkomen. In principe is er een record aanwezig waarin de 'officiele' versie staat die door een admin in het systeem is gezet. Door verschillende manieren om het op te schrijven kan er door een gebruiker echter een nieuw record aangemaakt zijn wat in feite dezelfde kist is. Het kan dus zo zijn dat de officiele versie 'FA-01' is, maar dat er ook ergens een record te vinden is met 'FA01', of 'FA 01'.
- in de tabel registration_search staan de opgeschoonde waardes van alleen de officiele records. Dus voor het record met 'FA-01' zal er een search waarde 'FA1' zijn. Als dus een gebruiker nu 'FA 01' intikt en zoekt naar een match dan zal die opgeschoonde waarde van 'FA 01' (wat 'FA1' is) matchen met het officiele record en zo kan de correcte link gelegd worden.
Misschien heb je het wel te veel versimpeld:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT a.plane_id, m.registration,
FROM planes a
INNER JOIN planes m ON (
(m.registration = a.registration OR m.registration = 'FA1')
AND m.plane_id <> a.plane_id
)
WHERE (m.country_id = a.country_id OR a.country_id = 0)
AND a.plane_id = 1)
FROM planes a
INNER JOIN planes m ON (
(m.registration = a.registration OR m.registration = 'FA1')
AND m.plane_id <> a.plane_id
)
WHERE (m.country_id = a.country_id OR a.country_id = 0)
AND a.plane_id = 1)
Is hetzelfde als
Code (php)
1
2
3
2
3
SELECT plane_id, registration
FROM planes
WHERE plane_id = 1
FROM planes
WHERE plane_id = 1
Of mis ik iets?
Gewijzigd op 12/05/2014 20:17:24 door Ger van Steenderen
Code (php)
1
2
3
4
2
3
4
plane_id | registration | country_id
----------+----------------+--------------
1 | FA01 | 0
2 | FA-01 | 1
----------+----------------+--------------
1 | FA01 | 0
2 | FA-01 | 1
resultaat mijn query:
geen
resultaat jouw query:
plane_id: 1
registration: FA01
Code (php)
1
2
3
4
2
3
4
plane_id | registration | country_id
----------+----------------+--------------
3 | 37+01 | 0
4 | 3701 | 1
----------+----------------+--------------
3 | 37+01 | 0
4 | 3701 | 1
resultaat mijn query:
plane_id: 3
registration: 3701
resultaat jouw query:
plane_id: 3
registration: 37+01
(merk op dat in dit geval de opgeschoonde registratie 3701 zou zijn, in plaats van FA1 dus)
Gewijzigd op 12/05/2014 21:45:24 door Erwin H
Erwin H op 12/05/2014 19:39:30:
- in de tabel registration_search staan de opgeschoonde waardes van alleen de officiele records. Dus voor het record met 'FA-01' zal er een search waarde 'FA1' zijn. Als dus een gebruiker nu 'FA 01' intikt en zoekt naar een match dan zal die opgeschoonde waarde van 'FA 01' (wat 'FA1' is) matchen met het officiele record en zo kan de correcte link gelegd worden.
- in de tabel registration_search staan de opgeschoonde waardes van alleen de officiele records. Dus voor het record met 'FA-01' zal er een search waarde 'FA1' zijn. Als dus een gebruiker nu 'FA 01' intikt en zoekt naar een match dan zal die opgeschoonde waarde van 'FA 01' (wat 'FA1' is) matchen met het officiele record en zo kan de correcte link gelegd worden.
Code (php)
1
2
3
4
5
2
3
4
5
INNER JOIN registration_search ms ON (
(ms.search_value = a.registration OR ms.search_value = 'FA1')
AND ms.plane_id <> a.plane_id
)
INNER JOIN planes mp ON ms.plane_id = mp.plane_id
(ms.search_value = a.registration OR ms.search_value = 'FA1')
AND ms.plane_id <> a.plane_id
)
INNER JOIN planes mp ON ms.plane_id = mp.plane_id
Hoe staat dan uit jou voorbeeld plane_id 3 in registration_search?
En voor de goede orde, ik heb inmiddels in een test omgeving de situatie met twee temporary tables draaien. Het werkt dus inmiddels wel, maar ik blijf me afvragen of het de beste oplossing is....
Je zou eventueel in SQL nog een functie kunnen maken voor het opschonen van de data, maar dat levert volgens mij ook weinig voordeel op.
In elk geval weer eens met wat nieuws wezen stoeien, dat is altijd al meteen een voordeel :-)
Dank voor je hulp!
1) extra (berekende) gegevens opslaan die niet of nauwelijks nodig zijn, maar als het nodig is dan wel iets sneller de zoekopdracht vervullen
2) minder data opslag, maar een wat complexere zoekopdracht
Ik kies voor 2, maar ik wil niet zeggen dat keuze 1 dus fout of slecht is. Het is mijn inschatting dat hoewel de zoekopdracht nu wat complexer is, het me uiteindelijk rekenkracht en opslag scheelt, omdat ik nu ietwat minder complexe inserts en updates hoef uit te voeren. Maar of het echt quantificeerbaar is is zeer de vraag.