[mysql] If en joins performance tov Php

[Anoniem: 98579]

Ik heb eigenlijk 2 vragen.

Eerst vraag:
Wat levert meer performance (wat gaat sneller, en wat levert het minste serverload op)
Zoveel mogelijk info uit 1 query halen mbv if's en joins, of in php gewoon 2 of 3 enkele queries uitvoeren, en daarin alle ifjes afhandelen.

En dan de 2e vraag, ik ga er eig van uit dat 1 query gebruiken het snelst is, en dan zal ik dus ook meteen met een praktisch voorbeeld komen, waar ik niet helemaal uit kom

Ik heb een table met usernames en passwords, en een userid. Nu staan daaronder weer 2 tables, een voor bedrijven en een voor niet-bedrijven, die zijn aan gelinkt aan de user table met user_id.

Nou is het eerste probleem, hoe weet je bij een inlog of het om een bedrijf gaat of niet. Je zou natuurlijk een extra veld kunnen maken in de user table, waar dat in staat. Maar dan heb je ook een extra query nodig om de info weer op te halen (dacht ik).

Daarom had ik dit bedacht:

MySQL:

SELECT IFNULL( bedrijvendata.naam, kandata.naam ) AS naam, userdata.user_id
FROM `bedrijvendata` , `kandata`
RIGHT JOIN `userdata` ON bedrijvendata.user_id = userdata.user_id
AND kandata.user_id = userdata.user_id
WHERE userdata.email = '[e-mail]'

Nou kan ik me voorstellen dat jullie dit heel erg lelijk vinden, ik ben er zelf ook nog niet helemaal van overtuigd, maar het werkt prima moet ik zeggen, omdat de user_id altijd maar 1 keer voorkomt in de 2 tabellen.

Vraag: wat vinden jullie van deze methode, en is het sneller / beter dan een extra veld aanmaken en die eerst ophalen.

Volgende vraag, ik wil deze query gelijk combineren met nog een if luss, namenlijk:

code:

SELECT IF( password ='pass5wrod' , 'yes', 'no' ) FROM userdata WHERE email = '[e-mail]'

Deze vond ik op internet, en leek me erg cool, want dat scheelt je weer extra code, want normaal haal ik eerst de shit op die bij het email adres hoort, en dan check ik het in php. Nu weer de vraag, zal dit sneller zijn? En de 2e vraag, hoe combineer ik deze met de vorige query, zodat als het password klopt met het emailadres, de eerste query wordt uitgevoerd, en dus meteen alle data wordt opgehaald.

Ik ben erg benieuwd nar jullie inzichten in deze

[Anoniem: 88197]

ff beter lezen

Die 1e Query is wel prima lijkt me... Dat ik het niet netjes vind, is een tweede, maar goed.

[ Voor 166% gewijzigd door Anoniem: 88197 op 09-02-2006 10:13 ]

[RedHat]

Dat tweede is best omslachtig. Normaal haal je info op, en op _alle_ info kun je controleren. Nu controleer je één ding die je OOK weer met if moet controleren (dus if (!query)) { echo null }

Dus waarom één dingetje controleren als je in één keer alle informatie op kan halen en dan checks gaan doen.

[Anoniem: 98579]

RedHat schreef op donderdag 09 februari 2006 @ 10:38:
Dat tweede is best omslachtig. Normaal haal je info op, en op _alle_ info kun je controleren. Nu controleer je één ding die je OOK weer met if moet controleren (dus if (!query)) { echo null }

Dus waarom één dingetje controleren als je in één keer alle informatie op kan halen en dan checks gaan doen.

Ja het is dus de vraag wat de beste performance levert, nu kijk ik al of het password klopt in mysql, en dat scheelt dus weer in PHP, en als ik dus de 2 queries kan combineren, ben ik helemaal klaar!
Dan heb ik een query nodig om te kijken of de user bestaat en het paswword klopt, en zo ja alle data ophaalt, dat scheelt echt veel php code. Dat is dus de reden dat ik vraag, wat gaat het snelst?

[Anoniem: 88197]

Je doet toch gewoon zo:

SQL:

SELECT ... 
WHERE email = '{$email}' AND password ='{$password}'

Als je dan 1 row terugkrijgt -> goed, 0 rows -> login failed

[Anoniem: 98579]

Hmm, ik weet niet of je dit bedoelde, maar ik kreeg iig het idee:

code:

SELECT IFNULL( bedrijvendata.naam, kandata.naam ) AS naam, userdata.user_id
FROM `bedrijvendata` , `kandata`
RIGHT JOIN `userdata` ON bedrijvendata.user_id = userdata.user_id
AND kandata.user_id = userdata.user_id
WHERE userdata.email = '[e-mail]' AND password = 'password'

Dat zou natuurlijk ook moeten werken, al is dit ook niet echt netjes....

Ik vraag me dan af, belast je mysql dan niet onnodig door te zoeken naar een entry (user_id in een vd tables) terwijl je em helemaal niet returned omdat het passwrod niet goed is. Mooier is natuurlijk dat ie pas de data select query uit voert, als de password query klopt....

[ Voor 33% gewijzigd door Anoniem: 98579 op 09-02-2006 11:10 ]

[Anoniem: 88197]

Anoniem: 98579 schreef op donderdag 09 februari 2006 @ 11:08:
Hmm, ik weet niet of je dit bedoelde, maar ik kreeg iig het idee:

code:

SELECT IFNULL( bedrijvendata.naam, kandata.naam ) AS naam, userdata.user_id
FROM `bedrijvendata` , `kandata`
RIGHT JOIN `userdata` ON bedrijvendata.user_id = userdata.user_id
AND kandata.user_id = userdata.user_id
WHERE userdata.email = '[e-mail]' AND password = 'password'

Dat zou natuurlijk ook moeten werken, al is dit ook niet echt netjes....

Waarom is dat niet netjes Als die combinatie niet voorkomt (van de WHERE-clause) is de combinatie e-mail en wachtworod niet correct...

[Anoniem: 98579]

Anoniem: 88197 schreef op donderdag 09 februari 2006 @ 11:11:
[...]

Waarom is dat niet netjes Als die combinatie niet voorkomt (van de WHERE-clause) is de combinatie e-mail en wachtworod niet correct...

nou ik dacht dus omdat:

Ik vraag me dan af, belast je mysql dan niet onnodig door te zoeken naar een entry (user_id in een vd tables) terwijl je em helemaal niet returned omdat het passwrod niet goed is. Mooier is natuurlijk dat ie pas de data select query uit voert, als de password query klopt....

[Anoniem: 88197]

Anoniem: 98579 schreef op donderdag 09 februari 2006 @ 17:55:
[...]


nou ik dacht dus omdat:

[...]

Hij zoekt op de combinatie... Het lijkt me dat dat wel geoptimaliseerd wordt op de een of andere manier, ik zou me om iets simpels niet al te druk maken.

[Soultaker]

Dit is een optimalisatieprobleem en daarvoor gelden in het algemeen altijd twee dingen:

• Ga pas optimaliseren nadat je hebt vastgesteld welk deel van de applicatie het traagst (of geheugenintensiefst) is.
• Staar je niet blind op vuistregels en vereenvoudigde modellen van efficiëntie, maar test de verschillende alternatieven zelf om uit te vinden wat in jouw situatie het meest efficiënt is.

Het is dan ook niet zinnig om van begin af aan de allerefficiënste code te willen schrijven, wanneer dat ten koste gaat van de helderheid of correctheid van de implementatie. Het is vaak aan te raden om de code zo te schrijven dat deze goed onderhoudbaar en overduidelijk correct is. Natuurlijk is het wel belangrijk om je databaseschema van begin af aan goed op te zetten, zodat je later gemakkelijk de code kunt aanpassen.

Anoniem: 98579 schreef op donderdag 09 februari 2006 @ 10:05:
Daarom had ik dit bedacht:

MySQL:

SELECT IFNULL( bedrijvendata.naam, kandata.naam ) AS naam, userdata.user_id
FROM `bedrijvendata` , `kandata`
RIGHT JOIN `userdata` ON bedrijvendata.user_id = userdata.user_id
AND kandata.user_id = userdata.user_id
WHERE userdata.email = '[e-mail]'

Nu weet je dus nog steeds niet of de naam die je terugkrijgt bij een bedrijf of een gebruiker hoort. Als dat ook niet uitmaakt, dan vind ik het vreemd dat je een deel van de gemeenschappelijke data voor 'bedrijven' en 'kandidaten' (?) hebt afgesplitst naar de 'user' tabel (namelijk de user_id en het e-mailadres) en de naam, die ze blijkbaar ook gemeen hebben, niet.

Als je de naam ook in de user tabel had gezet, had je dit probleem niet gehad. Verder vind ik de oplossing wel in orde, maar ik denk dat het alternatief (met twee of drie queries) niet veel minder efficiënt is (hoewel dat natuurlijk niet atomair is). Het is dus maar net de vraag wat je zelf handiger vind.

Vraag: wat vinden jullie van deze methode, en is het sneller / beter dan een extra veld aanmaken en die eerst ophalen.

Naar mijn mening: nee, het is niet overduidelijk sneller/beter.

Volgende vraag, ik wil deze query gelijk combineren met nog een if luss, namenlijk:

code:

SELECT IF( password ='pass5wrod' , 'yes', 'no' ) FROM userdata WHERE email = '[e-mail]'

Deze vond ik op internet, en leek me erg cool, want dat scheelt je weer extra code, want normaal haal ik eerst de shit op die bij het email adres hoort, en dan check ik het in php. Nu weer de vraag, zal dit sneller zijn?

Ook bij deze vraag is het zinnig om de punten toe te passen die ik in het begin noemde. Maakt het uit? Het is waarschijnlijk geen performance bottleneck (1), dus waarschijnlijk niet. Is het sneller? Dat kun je alleen zeker weten door het uit te proberen (2) - mijn intuïtie zegt dat PHP en MySQL op ongeveer dezelfde manier dit soort expressies evalueren (ze moeten 'm eerst parsen en dan interpreter code uitvoeren) dus het zal weinig uitmaken. Vermoedelijk is PHP marginaal sneller, maar er spelen een heleboel zaken mee waarover je geen gedetailleerde kennis hebt (denk aan de MySQL query parser, het netwerkprotocol tussen client en server, enzovoorts) en alleen door het te meten kun je het verschil vaststellen.

[Anoniem: 98579]

[b][message=25179219,noline]Soultaker schreef op donderdag 09 februari 2006 @
Nu weet je dus nog steeds niet of de naam die je terugkrijgt bij een bedrijf of een gebruiker hoort. Als dat ook niet uitmaakt, dan vind ik het vreemd dat je een deel van de gemeenschappelijke data voor 'bedrijven' en 'kandidaten' (?) hebt afgesplitst naar de 'user' tabel (namelijk de user_id en het e-mailadres) en de naam, die ze blijkbaar ook gemeen hebben, niet.

Als je de naam ook in de user tabel had gezet, had je dit probleem niet gehad. Verder vind ik de oplossing wel in orde, maar ik denk dat het alternatief (met twee of drie queries) niet veel minder efficiënt is (hoewel dat natuurlijk niet atomair is). Het is dus maar net de vraag wat je zelf handiger

Wat betreft het probleem dat ik dan nog steeds niet weet of het een bedrijf is of niet, dat ben ik helemaal met je eens. Ik ben dus ook niet helemaal gelukkig met de oplossing.
Maar normaal zou ik wss bedrijvendata.* (EDIT: dit lijkt dus niet te werken) selecteren, en dan kan ik natuurlijk makkelijk onderscheid maken tussen bedrijf en en kandidaat omdat de data anders is, maar dat is ook niet echt mooi.

De reden dat ik alleen email en password heb afgesplitst is dat ik alleen die fields gebruik bij het inloggen (en ik dus maar 1 table hoef te gebruiken), en als die correct is kan ik de bijbehorende data ophalen. Ik moet na een correcte login toch de aanvullende info ophalen, dus ik zie niet helemaal wat het voordeel zou zijn van alle gemeenschappelijke velden in de userdata table te zetten.

Nog een comentaar op deze code:

code:

SELECT IFNULL( bedrijvendata.naam, kandata.naam ) AS naam, userdata.user_id
FROM `bedrijvendata` , `kandata`
RIGHT JOIN `userdata` ON bedrijvendata.user_id = userdata.user_id
AND kandata.user_id = userdata.user_id
WHERE userdata.email = '[e-mail]' AND password = 'password'

Hiermee zie ik dus niet, als er 0 rows terug komen, waar het aan ligt, aan het password of aan het email adres. Dat is dus de reden dat ik nog een if lus in de query wil, want dan kan ik als het email adres niet klopt een andere return value geven dan null. En weet ik dus of het aan het email adres ligt of aan het password. Maar als ik de algemene toon hier hoor kan ik net zogoed de query weer opbreken in meerdere queries, qua leesbaarheid en bruikbaarheid....

Verder over de optimalisatie, het klopt idd wel dat ik me er nu een beetje druk over maak. Maar ik lees overal dat mysql altijd de bottleneck vormt, en dat je het beste zo min mogelijk queries kan gebruiken. Ik maak me er misschien overdreven druk over nu, maar ik wil ook een beetje geoefend raken in het optimaliseren.

[ Voor 25% gewijzigd door Anoniem: 98579 op 11-02-2006 10:53 ]

[Soultaker]

Als je in bovenstaande query gewoon bedrijvendata.naam en kandata.naam afzonderlijk ophaalt, dan kun je makkelijk zien of de gevonden entry een bedrijf is of een kandidaat.

Bovendien kun je het 'AND password=...' uit de query halen. Als je dan geen rij terugkrijgt weet je dat de gebruiker niet bestaat (tenminste volgens de 'userdata' tabel). Het wachtwoord controleer je dan handmatig in PHP. (Effectief komt dat ook neer op de oplossing van SELECT name, (password = 'whatever') AS PasswordCorrect maar waarom zou je dit soort dataverwerking in SQL doen?)

Verder over de optimalisatie, het klopt idd wel dat ik me er nu een beetje druk over maak. Maar ik lees overal dat mysql altijd de bottleneck vormt, en dat je het beste zo min mogelijk queries kan gebruiken. Ik maak me er misschien overdreven druk over nu, maar ik wil ook een beetje geoefend raken in het optimaliseren.

Dat klopt, maar daarom is het wel zinnig om bij een pageview (in het geval van webapplicaties) een lijstje te genereren met queries die je uitvoert en hoe kostbaar ze zijn (te meten in executietijd, analyse die MySQL EXPLAIN geeft, en aantal resultaten wat ze opleveren). Grote kans dat simpele queries als deze de bottleneck niet vormen.

Leren optimaliseren is natuurlijk altijd verstandig, maar dat doe je in mijns inziens het beste door eerst goed inzicht te krijgen in hoe 'duur' verschillende queries zijn. Een enkele rij selecteren op basis van een index is namelijk niet kostbaar; carthesische producten of selecties die niet met behulp van indices opgelost kunnen worden zijn dat meestal wel.

[ Voor 7% gewijzigd door Soultaker op 11-02-2006 16:47 ]

[Anoniem: 64486]

Gewoon :

PHP:

$user = $_POST['user'];
$pass = $_POST['pass'];
$sql = "SELECT * FROM whatever WHERE user='$user' AND pass='$pass'";
$query = mysql_query($sql);

If (!$query){
Foutieve user en/of password
}
else
{
....

[NMe]

Als je toch bezig bent met dergelijke optimalisaties, herschrijf dan meteen ook je right join naar een left join, aangezien (oudere versies van) MySQL die beter kan optimaliseren.

@olger1024: zoek eens op wat SQL injectie is. En verder geef ik persoonlijk de voorkeur aan het gebruik van mysql_num_rows in plaats van kijken of mysql_query iets returnt wat naar false evaluëert.

[Soultaker]

Het is geen kwestie van voorkeur - mysql_query() retourneert gewoon altijd true als de query correct uitgevoerd wordt, dus ook als de gebruiker of het wachtwoord niet klopt. Enig verschil is dat de query dán geen resultaatregels oplevert.

I.m.o. is zo'n constructie dan ook prima (zonder mysql_num_rows):

PHP:

$res = mysql_query($sql) or die('Unexpected failure of MySQL query!');
$row = mysql_fetch_array($res);
if($row) {
  // Rij gevonden
} else {
  // Geen resultaat: onbekende user of incorrect password
}

Een expliciete controle op FALSE zou kunen ($row !== FALSE) maar is hier niet echt nodig omdat een geldig resultaat toch altijd een array is.

Maar de TS weet dit allemaal al, dus waar hebben we het over.

[NMe]

Soultaker schreef op zaterdag 11 februari 2006 @ 20:56:
Het is geen kwestie van voorkeur - mysql_query() retourneert gewoon altijd true als de query correct uitgevoerd wordt, dus ook als de gebruiker of het wachtwoord niet klopt.

Dat geldt alleen voor queries die mutaties doen op de database.

quote: http://nl3.php.net/mysql_query

Alleen voor SELECT, SHOW, EXPLAIN en DESCRIBE queries geeft mysql_query() een resource identifier terug of FALSE als de query niet correct werd uitgevoerd.

Volgens mij is de resource identifier een null-pointer op het moment dat er een lege recordset wordt teruggegeven, dus voor zover ik weet mag de code van olger1024 wel. Maar netjes is anders.

[martijnve]

Anoniem: 64486 schreef op zaterdag 11 februari 2006 @ 18:57:
Gewoon :

PHP:

$user = $_POST['user'];
$pass = $_POST['pass'];
$sql = "SELECT * FROM whatever WHERE user='$user' AND pass='$pass'";
$query = mysql_query($sql);

If (!$query){
Foutieve user en/of password
}
else
{
....

het werk wel maar warom sla je je result op een een variabele genaamd $query???
lijkt me behoorlijk mislijdend.

PHP:

$query = "SELECT * FROM whatever WHERE user='$user' AND pass='$pass'";
$result = mysql_query($query);

lijkt me een stuk logischer.

[Anoniem: 98579]

Hm we gaan een beetje offtopic in op iemands anders query in, die is niet echt relevant want ik wil wat meer specificatie in de error.

Hoe dan ook heb ik eigenlijk nog niet helemaal de oplossing waar ik naar op zoek was, maar omdat de meeste van jullie van mening zijn dat er weinig tijdwinst in te halen is heb het zo gedaan:

PHP:

sprintf("SELECT IF( password =%s , type , 'no' ) as result, user_id, email FROM userdata  WHERE email = %s",
            quote_smart(sha1($password)),
            quote_smart($email)));

Hiermee weet ik precies of het password niet klopt of het email adres. Als ie klopt heb ik maar een query nodig om de rest op te halen. (Ik heb dus besloten een extra veld in userdata te zetten)

Ik vind de oplossing minder mooi, maar twerd wel erg ingewikkeld en ik haalde er weinig winst uit. Als iemand alsnog een mooiere oplossing heeft dan hoor ik het graag. Maar zoals Soultaker volgens mij al zei, er valt weinig winst te halen op een query die van toepassing is op geindexeerde velden.

Ik zou in dit topic wel graag nog wat meer vragen stellen mbt optimalisatie. Namelijk, als ik een mysql-class heb, die een db connectie opent, en voor een table alle fields ophaalt etc.
Ik genereer forms aan de hand van de fields in de db, en dus open ik een instance van die mysql-class. Is het dan beter om bij het genereren van die form, de class in een sessie op te slaan. En die dan weer op te halen bij het opslaan vd form. Of kost dat teveel geheugen en ik kan ik msschien beter de class opnieuw openen.

[flowerp]

Anoniem: 64486 schreef op zaterdag 11 februari 2006 @ 18:57:
Gewoon :

PHP:

$user = $_POST['user'];
$pass = $_POST['pass'];
$sql = "SELECT * FROM whatever WHERE user='$user' AND pass='$pass'";
$query = mysql_query($sql);

If (!$query){
Foutieve user en/of password
}
else
{
....

Ik denk dat dit stukje code brilliant weergeeft waarom PHP vaak (mischien onterecht) zo'n slechte naam heeft: business logic, control, (gebrek aan) validatie en layout/gui allemaal door elkaar heen op de pagina.

[Olaf van der Spek]

Anoniem: 98579 schreef op zondag 12 februari 2006 @ 10:53:

PHP:

sprintf("SELECT IF( password =%s , type , 'no' ) as result, user_id, email FROM userdata  WHERE email = %s",
            quote_smart(sha1($password)),
            quote_smart($email)));

Wat is type precies?
En waarom heb je email als zowel input (where) als output?

[Anoniem: 98579]

OlafvdSpek schreef op zondag 12 februari 2006 @ 11:52:
[...]

Wat is type precies?
En waarom heb je email als zowel input (where) als output?

ROFL! Waarom selecteer ik email, das een goeje vraag, die halen we er maar even uit

En type is het type dat de gene die inlogged is. Nl een bedrijf of kandidaat. Dus daarmee bepaald ik uit welke table de aanvullende info moet worden opgehaald. Ik ben dus toch overstag gegaan voor die methode

[Olaf van der Spek]

Anoniem: 98579 schreef op zondag 12 februari 2006 @ 12:00:
ROFL! Waarom selecteer ik email, das een goeje vraag, die halen we er maar even uit

En type is het type dat de gene die inlogged is. Nl een bedrijf of kandidaat. Dus daarmee bepaald ik uit welke table de aanvullende info moet worden opgehaald. Ik ben dus toch overstag gegaan voor die methode

Ik snap niet waarom je password valid en type in een kolom stopt.

[Anoniem: 98579]

OlafvdSpek schreef op zondag 12 februari 2006 @ 12:20:
[...]

Ik snap niet waarom je password valid en type in een kolom stopt.

Is geen goede reden voor, eerst was result het enige wat ik returnde, maar toen bleek dat ik user_id ook nodig had. Het id was eerst dat ik het type terug kreeg, of no wanneer het password incorrect was. Ik kan type wel vervangen door 'yes' oid en dan type ook nog returnen, maar waarom zou ik nog een extra return waarde willen, aan de waarde 'yes' heb ik nix natuurlijk, dus kan ik het net zo goed zo doen.

[Soultaker]

-NMe- schreef op zaterdag 11 februari 2006 @ 22:48:
Dat geldt alleen voor queries die mutaties doen op de database.

De boolean waarde true wel, maar het resultaat evalueert ook naar 'true' voor andere queries:

For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_query() returns a resource on success, or FALSE on error.
For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

Bij een fout retourneert mysql_query() dus FALSE. Bij succes ofwel TRUE ofwel een resource. Als je een resource converteert naar een boolean (wat gebeurt in if-statement) dan evalueert die altijd naar true.

Volgens mij is de resource identifier een null-pointer op het moment dat er een lege recordset wordt teruggegeven, dus voor zover ik weet mag de code van olger1024 wel. Maar netjes is anders.

Ik weet niet wat je bedoelt met 'null pointer' in deze context, maar volgens de handleiding levert een succesvolle query altijd een geldige resource identifier op - ook als de result set leeg is (dat kun je ook simpel in de praktijk verifiëren).

De eindconclusie blijft: het resultaat van mysql_query evalueert naar true wanneer en alleen wanneer de query met succes uitgevoerd is - ook als er geen resultaten beschikbaar zijn.

[Anoniem: 64486]

-NMe- schreef op zaterdag 11 februari 2006 @ 19:05:
Als je toch bezig bent met dergelijke optimalisaties, herschrijf dan meteen ook je right join naar een left join, aangezien (oudere versies van) MySQL die beter kan optimaliseren.

@olger1024: zoek eens op wat SQL injectie is. En verder geef ik persoonlijk de voorkeur aan het gebruik van mysql_num_rows in plaats van kijken of mysql_query iets returnt wat naar false evaluëert.

Sorry, was de addslashes vergeten

PHP:

$user = addslashes($_POST['username']);