Multi Query Insert

De database-oplossing en query-strategie hangt in ieder geval sterk af van de applicatie. Als je vaak queries hebt met inserts met meerdere (grote) sets van values dan wordt alles nogal traag volgens mij. Daarnaast zou je dit kunnen stresstesten om te zien wat beter werkt. Meten = weten.

Maar terug naar het oorspronkelijke onderwerp, bovenstaande aanpak gaat niet werken omdat je met verschillende tabellen te maken hebt.

Wacht eens, als ik nu alles in één tabel sto... :p

Op mijn werk worden er om verschillende redenen behoorlijk wat records rondgepompt. Daar heb ik allerlei processen dramatisch kunnen versnellen door losse inserts of inserts via prepared statements om te schrijven naar bulk inserts (insert met meerdere values of insert select). Ik praat dan over inserts die variëren van 1200 tot meer dan een half miljoen records per statement.

Overigens, zonder het achterliggend datamodel te kennen: als ik kijk naar de queries in de original post, vind ik het goed te verdedigen om alles in 1 tabel te stoppen.

Willem vp op 14/02/2017 08:43:43

Overigens, zonder het achterliggend datamodel te kennen: als ik kijk naar de queries in de original post, vind ik het goed te verdedigen om alles in 1 tabel te stoppen.

Zo te zien hoort alles inderdaad in één tabel:

<?php
        // Insert in database
        $SQLInsertUser = "INSERT INTO users 
                         (email, password, reg_date, ipaddress, verified)
                         VALUES 
                         ('" . $FormEmail . "', '" . $FormPassword . "', '" . $FormDate . "', '" . $FormIpaddress . "', 'no');";
        
        $SQLInsertUser .= "INSERT INTO verify 
                          (email, hash2)
                           VALUES 
                          ('" . $FormEmail . "', '" . $RandomHash . "')";
?>

Ik mis hier verschillende dingen:

• De tweede tabel 'verify' bevat alleen een "hash" (die eigenlijk geen hash is maar een random token) bij het e-mailadres dat ook voorkomt in de eerste tabel.

• Er wordt niet opgeslagen wanneer die "hash" voor de verificatie wordt gegenereerd, dus er kunnen kennelijk tot in alle eeuwigheid ongeverifieerde e-mailadressen circuleren.

• Er is geen primaire sleutel die de twee tabellen koppelt (tenzij dat het e-mailadres is, maar dat is om diverse redenen niet handig).

• In de eerste tabel staat de kolom 'verified' op de string 'no', maar daarvan kun je beter een flag maken van 1 bit, bijvoorbeeld een TINYINT(1), of een TIMESTAMP als je het verschil tussen de registratiedatum en de verificatiedatum wilt kunnen gebruiken. Als je security by design toepast, zou de veiligste toestand 'verified' == 'no' bovendien de standaardtoestand moeten zijn — en kun je die weglaten in de query.

• Als je toch in één keer twee queries wilt uitvoeren voor een INSERT van één gebruiker, kun je hier veel beter een transactie gebruiken dan een multiquery.

Ik wil liever niet alles in een tabel omdat ik de user tabel klein wil houden met alleen belangrijke gegevens. Wanneer iemand zijn emailadres heeft geverifieerd wordt de entry van de verify tabel ook weer netjes verwijderd.

In beide tabbelen staat email op UNIQUE dus ik krijg geen eeuwigheid aan ongeverifieerde emailadressen. Tevens heeft de tabel verify een TIMESTAMP kolom die update on entry. Ik kan dus altijd zien wanneer dit is gebeurd en kan dit eventueel na twee weken weer verwijderen. Ik geef ook in de email aan dat je binnen twee weken je email moet verifieren.

Beide tabbelen hebben ook een primary key "id".

Wat betreft verified = 'no' dat kan ik veranderen naar de tinyint. Bedankt voor de tip.

Ik ga de transacties tevens toevoegen waar Ariën een voorbeeld van stuurde.

Danny von Gaal op 14/02/2017 09:46:25

Ik wil liever niet alles in een tabel omdat ik de user tabel klein wil houden met alleen belangrijke gegevens. Wanneer iemand zijn emailadres heeft geverifieerd wordt de entry van de verify tabel ook weer netjes verwijderd.

Je kunt die kolom gerust naar de hoofdtabel users verplaatsen. Als je de kolom nullable maakt met DEFAULT NULL, ga je pas bij honderdduizenden gebruikers verschil merken. In ruil daarvoor krijg je in een veel eerder stadium al een performance-winst, omdat je niet meer twee queries hoeft uit te voeren.

Danny von Gaal op 14/02/2017 09:46:25

In beide tabbelen staat email op UNIQUE dus ik krijg geen eeuwigheid aan ongeverifieerde emailadressen.

Daar moet je voorzichtig mee zijn, merkte ik afgelopen week weer toen een teamlid me met mijn Gmail-adres aan Slack had toegevoegd. Voor Google maakt het niet uit of je een e-mailadres schrijft als [email protected] met hoofdletters, [email protected] met kleine letters of [email protected] en [email protected] zonder punt. Voor Google is dat één e-mailadres, maar maak je de kolom UNIQUE, dan zijn het verschillende e-mailadressen. Bij Slack hadden ze daarmee ook geen rekening gehouden, dus ik kon niet inloggen mét een geldig e-mailadres.

Oké dank je daar kan ik wat mee.

En los van dat de kolom op Unique staat kan ik eerst alle letters lowercase maken voor ik het insert anders. Met en zonder punt zijn voor mij wel andere emailadressen.

@ward: Zo ver ik weet zijn ALLE emailadressen case INsensitive. Dus komt [email protected] gewoon aan bij [email protected]. Dus zou lowercase maken inderdaad een hoop problemen kunnen vermijden.


(Wéér een reden om OOP te programmeren, hoef je enkel maar je setter aan te passen)

Frank Nietbelangrijk op 14/02/2017 13:23:29

@ward: Zo ver ik weet zijn ALLE emailadressen case INsensitive.

Het gedeelte vóór de @ mag volgens de RFC case-sensitive zijn. In de praktijk ben ik nog geen server tegengekomen die dat onderscheid maakt.

Edit:
Je moet overigens niet zoeken op de term 'case sensitive':

the local-part MUST be interpreted and assigned semantics only by the host specified in the domain part of the address.

Wat hier eigenlijk staat, is dat software die email-adressen verwerkt niet kan weten hoe de ontvangende mailserver met het local-part van een adres omgaat (het gedeelte voor de @ dus). Zoals Ward al beschreef kan het zijn dat punten in het local-part niet relevant zijn. Maar ook hoofdlettergevoeligheid valt daaronder.

Edit:
Nog een reden waarom het niet heel veel nut heeft om een unique constraint op email-adressen te zetten is RFC 5233. Ruwweg samengevat wordt daarin beschreven dat gebruiker+blabla@ een alias (sub-adres) is van gebruiker@.

Dat is goed om te weten Willem. Bedankt voor jouw bijdrage.

Het zijn naar mijn idee beiden geen zaken om je heel erg druk over te maken maar je zou in theorie kunnen meemaken dat een bedrijf de volgende mensen en mailadressen in gebruik heeft:

John Doe [email protected]
Jens Doe [email protected]

Maar als dat zo is zullen ze daar snel vanaf willen denk ik want dan gaat het wel heel vaak mis of niet?