[MySql] Sql-query probleem

[barteke]

Hallo,

Kan iemand mij vertellen waarom ik geen output krijg van onderstaande query, maar in plaats daarvan m'n systeem vastloopt. (Windows XP, PHP 4, Mysql 3.23.44)

Het gaat om een tabel met 415 rijen, moet geen probleem zijn lijkt me zo.

werkt niet:


Select distinct cv.categorie,o.cv_id,e.titel,cv.beschrijving from opleiding o,ervaring e,cv where (o.opleidingsniv_1='HBO' OR o.opleidingsniv_2 = 'HBO') Or (e.functie_cat='Architectuur') AND e.cv_id=o.cv_id AND cv.cv_id=o.cv_id AND cv.cv_online='ja' order by cv.update_datum desc limit 0,5

Syntactisch is ie toch wel in orde lijkt me, ik krijg geen foutmeldingen.

Onderstaande queries werken wel, volgens mij zit het em in die extra "Or" voorwaarde in de Where-clause..?

werken wel:


Select distinct cv.categorie,o.cv_id,e.titel,cv.beschrijving from opleiding o,ervaring e,cv where (o.opleidingsniv_1='HBO' OR o.opleidingsniv_2 = 'HBO') AND e.cv_id=o.cv_id AND cv.cv_id=o.cv_id AND cv.cv_online='ja' order by cv.update_datum desc limit 0,5

Select distinct cv.categorie,o.cv_id,e.titel,cv.beschrijving from opleiding o,ervaring e,cv where (o.opleidingsniv_1='HBO' OR o.opleidingsniv_2 = 'HBO') And (e.functie_cat='Architectuur') AND e.cv_id=o.cv_id AND cv.cv_id=o.cv_id AND cv.cv_online='ja' order by cv.update_datum desc

Help iemand?

grtz

Bart

[gorgi_19]

Hoe zitten je indexen?

[barteke]

#
# Tabel structuur voor tabel `cv`
#

CREATE TABLE `cv` (
`cv_id` smallint(4) unsigned NOT NULL auto_increment,
`pin` smallint(4) unsigned NOT NULL default '0',
`bedr_bin` smallint(4) NOT NULL default '0',
`datum` date NOT NULL default '0000-00-00',
`opleiding` text NOT NULL,
`werkervaring` text NOT NULL,
`hobbies` text NOT NULL,
`motivatie` text NOT NULL,
`cv_online` char(3) NOT NULL default '',
`matchprof` char(3) NOT NULL default '',
`emailservice` char(3) NOT NULL default '',
`categorie` varchar(100) NOT NULL default '',
`update_datum` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`cv_id`),
UNIQUE KEY `cv_id` (`cv_id`)
) TYPE=MyISAM AUTO_INCREMENT=515 ;


#
# Tabel structuur voor tabel `ervaring`
#

CREATE TABLE `ervaring` (
`cv_id` smallint(4) unsigned NOT NULL default '0',
`pin` smallint(4) unsigned NOT NULL default '0',
`bedr_bin` smallint(4) NOT NULL default '0',
`ervarings_niveau` varchar(20) NOT NULL default '',
`functie_cat` varchar(40) NOT NULL default '',
`functie` varchar(40) NOT NULL default '',
`werk_erv` date NOT NULL default '0000-00-00',
`titel` varchar(100) NOT NULL default '',
`beschrijving` text NOT NULL,
`dienstverband` varchar(100) NOT NULL default '',
`update_datum` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`cv_id`),
UNIQUE KEY `ervaring_id` (`cv_id`)
) TYPE=MyISAM;


#
# Tabel structuur voor tabel `opleiding`
#

CREATE TABLE `opleiding` (
`cv_id` smallint(4) unsigned NOT NULL default '0',
`pin` smallint(4) unsigned NOT NULL default '0',
`bedr_bin` smallint(4) NOT NULL default '0',
`opleidingsniv_1` varchar(20) NOT NULL default '',
`afstudrich_1` varchar(50) NOT NULL default '',
`opleidingsniv_2` varchar(20) NOT NULL default '',
`afstudrich_2` varchar(50) NOT NULL default '',
PRIMARY KEY (`cv_id`),
UNIQUE KEY `opleiding_id` (`cv_id`),
FULLTEXT KEY `afstudrich_1` (`afstudrich_1`)
) TYPE=MyISAM;

[GlowMouse]

Je hele systeem loopt vast? Kijk of je bij taakbeheer nog kunt kijken wat MySQL met je CPU en RAM doet.

Ik weet niet hoeveel rijen je terug krijgt. Als dat er niet zoveel zijn, kun je proberen indexen in te stellen.

[barteke]

Op dat moment staat m'n CPU roodgloeiend (100 %)

Ik denk dat die query niet meer dan 100 rijen teruggeeft.

[jan-marten]

Ik zou hier eens naar kijken:
FAQ @ joins.

Ik denk dat hij alle 400 records gaat matchen. Ik heb hier ook last van gehad.

Bij het (simpel) doorvoeren van een LEFT JOIN ipv een FROM tabel1, tabel2 ging ik van 20 seconden naar 0,2 seconden.

pseudo:
SELECT .... FROM tabel1 LEFT JOIN tabel2 ON (tabel1.ref = tabel2.key) WHERE ...

[ACM]

barteke schreef op 01 januari 2004 @ 22:48:
Hallo,

Kan iemand mij vertellen waarom ik geen output krijg van onderstaande query, maar in plaats daarvan m'n systeem vastloopt. (Windows XP, PHP 4, Mysql 3.23.44)

Het gaat om een tabel met 415 rijen, moet geen probleem zijn lijkt me zo.

werkt niet:


Select distinct cv.categorie,o.cv_id,e.titel,cv.beschrijving from opleiding o,ervaring e,cv where (o.opleidingsniv_1='HBO' OR o.opleidingsniv_2 = 'HBO') Or (e.functie_cat='Architectuur') AND e.cv_id=o.cv_id AND cv.cv_id=o.cv_id AND cv.cv_online='ja' order by cv.update_datum desc limit 0,5

Ow, als ie correct is (dus niet een syntax error oplevert) werkt ie wel hoor...

Maar ik gok dat je wel een dom foutje hebt gemaakt. Herschrijf je query maar es tot:

SQL:

SELECT DISTINCT
  cv.categorie,
  o.cv_id,
  e.titel,
  cv.beschrijving
FROM
  opleiding o,
  ervaring e,
  cv
WHERE
  (o.opleidingsniv_1='HBO' OR o.opleidingsniv_2 = 'HBO')
  OR
  (e.functie_cat='Architectuur')
  AND
  e.cv_id=o.cv_id
  AND
  cv.cv_id=o.cv_id
  AND
  cv.cv_online='ja'
ORDER BY cv.update_datum DESC
LIMIT 0,5

Volgens mij wil je die OR niet op hetzelfde niveau als de joining-AND's, want daardoor krijg je dus een hele fijne crossjoin (zie de al eerder aangehaalde FAQ) die enorm zwaar is voor je database...

Wellicht is het schrijven van expliciete joins in dit geval wat veiliger:

SQL:

SELECT DISTINCT
  cv.categorie,
  o.cv_id,
  e.titel,
  cv.beschrijving
FROM
  cv -- die eerst! zie volgende comments
  INNER JOIN opleiding o ON cv.cv_id = o.cv_id
  INNER JOIN ervaring e ON cv.cv_id = e.cv_od -- het zou weinig uit mogen maken
      --, maar twee keer aan dezelfde (cv.cv_id in dit geval bijv)
      -- joinen is meestal veiliger
WHERE
  cv.cv_online = 'ja' -- Voor ja/nee hebben ze trouwens booleans bedacht
                            -- scheelt storage
  AND
  ( -- Ik gok dat dit de and/or-or structuur is die je graag wil?
    (o.opleidingsniv_1='HBO' OR o.opleidingsniv_2 = 'HBO')
       -- Volgens mij kan je die ook herschrijven als
       -- 'HBO' IN (o.opleidingsniv_1, o.opleidingsniv_2)
    OR
    e.functie_cat='Architectuur'
  )
ORDER BY cv.update_datum DESC
LIMIT 0,5

[ Voor 3% gewijzigd door ACM op 02-01-2004 01:25 ]

[ACM]

jan-marten schreef op 01 januari 2004 @ 23:49:
Bij het (simpel) doorvoeren van een LEFT JOIN ipv een FROM tabel1, tabel2 ging ik van 20 seconden naar 0,2 seconden.

Als er als voorbeeld query iets als dit staat:
SELECT ... FROM tabelA, tabelB WHERE tabelA.id = tabelB.id

Waarom geef je dan als vervangende optie de LEFT JOIN
Dan zou je toch de INNER JOIN moeten aanreiken... D'r zit nogal een verschil in werking in

En als je query met de LEFT JOIN correct is, dan was ie eerst fout (of dus vice versa), een goed geformuleerde query met de equi-join (zo'n =-constructie) wordt zelfs in MySQL meestal exact hetzelfde als de versie met INNER JOIN uitgevoerd.

[ Voor 21% gewijzigd door ACM op 02-01-2004 01:22 ]

[Verwijderd]

ACM schreef op 02 januari 2004 @ 01:18:

Is er een reden waarom jij 'cv' in de FROM zet, en niet 'opleiding' of 'ervaring'? In dit geval kunnen in principe elk van de 3 tabellen in de FROM (en de andere 2 joinen), dus ik vroeg mij af waarom jij 'cv' kiest of zou kiezen.

[ACM]

Verwijderd schreef op 02 januari 2004 @ 01:24:
[...]
Is er een reden waarom jij 'cv' in de FROM zet, en niet 'opleiding' of 'ervaring'? In dit geval kunnen in principe elk van de 3 tabellen in de FROM (en de andere 2 joinen), dus ik vroeg mij af waarom jij 'cv' kiest of zou kiezen.

Niet echt, maar aangezien zowel opleiding als ervaring aan de cv_id worden gekoppelt lijkt me het het meest correct om dan ook cv als 'hoofdtabel' te zien. Een goed database zal dan achteraf bepalen welke join ie eerst uitvoert (mysql kan dat trouwens niet zo goed, dus wellicht is dat een reden te experimenteren met de volgorde!)

[Verwijderd]

ACM schreef op 02 januari 2004 @ 01:27:
[...]

Niet echt, maar aangezien zowel opleiding als ervaring aan de cv_id worden gekoppelt lijkt me het het meest correct om dan ook cv als 'hoofdtabel' te zien.

Akkoord, maar 'opleiding' of 'ervaring' konden evengoed hoofdtabel zijn; het is maar hoe je de joins opschrijft.

Een goed database zal dan achteraf bepalen welke join ie eerst uitvoert (mysql kan dat trouwens niet zo goed, dus wellicht is dat een reden te experimenteren met de volgorde!)

Inderdaad, join-ordering heb ik in de laatste maanden geleerd, en ik vroeg mij hier af of jij een bepaald truckje had (door 'cv' te kiezen), zodat je de database al een zetje in de goede richting kon geven naar de beste volgorde

[ACM]

Je hebt in wezen twee keuzen:
A = B en A = C (en dan maar hopen dat je DB B = C afleidt, indien nodig)
of A = B en B = C (en dan maar hopen dat je DB A = C afleidt, indien nodig)

In mijn ervaring is de eerste de beste keuze, meestal...
Dus nee, er is niet echt een foefje voor. Je zult waarschijnlijk het meeste winnen door de meest-selectieve constructies eerst te doen. Dus als er weinig opleidingen zijn, die eerst en daar de rest tegen aan te plakken, etc.

MySQL heeft niet zo'n sterke query-planner waardoor het aardig wat werk vergt om een optimaal plan te forceren, met als gevolg dat je dus je joins in een leuke volgorde zult moeten zetten en/of zelfs je where-clauses anders moet formuleren.

[ Voor 21% gewijzigd door ACM op 02-01-2004 01:37 ]

[Verwijderd]

ACM schreef op 02 januari 2004 @ 01:36:
Je hebt in wezen twee keuzen:
A = B en A = C (en dan maar hopen dat je DB B = C afleidt, indien nodig)
of A = B en B = C (en dan maar hopen dat je DB A = C afleidt, indien nodig)

In mijn ervaring is de eerste de beste keuze, meestal...
Dus nee, er is niet echt een foefje voor. Je zult waarschijnlijk het meeste winnen door de meest-selectieve constructies eerst te doen. Dus als er weinig opleidingen zijn, die eerst en daar de rest tegen aan te plakken, etc.

MySQL heeft niet zo'n sterke query-planner waardoor het aardig wat werk vergt om een optimaal plan te forceren, met als gevolg dat je dus je joins in een leuke volgorde zult moeten zetten en/of zelfs je where-clauses anders moet formuleren.

Oké, thanks voor de uitleg

[Verwijderd]

Het probleem zit hem in de FROM a,b,c, etc

De komma doet een full outer join of hoe dat ook heet, dat is een produkt van alle mogelijkheden, dus a * b * c * etc
Dus als tabellen a, b en c elk 500 records hebben maakt ie eerst een hulptabel met 500 * 500 * 500 records = veel te veel = duurt erg lang = lijkt te hangen.

Er zijn databases die wel doorhebben wat je bedoelt en zelf wel de nodige optimalisaties uitvoeren, maar mysql is nogal 'dom' wat dat betreft en doet precies wat je vraagt (namelijk eerst het volledige produkt van alle gespecificeerde tabellen aanmaken en dan pas de overige query-constraints daarop toepassen).

De oplossing: het lijkt logisch, maar het is helemaal niet nodig alle tabellen waaruit je data nodig hebt achter de FROM te zetten; SELECT a.x, b.y, c.z FROM a xxxJOINyyy, met uiteraard nog wel de nodige joins etc waarin tabel b en c genoemd worden (zie alle replies hierboven), werkt ook en voorkomt dus het aanmaken van de mega-grote hulptabellen waar ie zo traag van wordt.

[barteke]

schitterend!


dank u allen zeer.