Hulp bij mysql setup en search query

[Cameron Diaz]

Hoi,

Ik heb een database-structuur met 5 tables.

SQL:

CREATE TABLE `sites` (
  `id` int(11) NOT NULL auto_increment,
  `sn` varchar(20) NOT NULL default '',
  `ln` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=560 DEFAULT CHARSET=latin1

CREATE TABLE `groups` (
  `id` int(20) NOT NULL auto_increment,
  `name` varchar(150) NOT NULL default '',
  `siteid` int(20) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `siteid` (`siteid`)
) ENGINE=MyISAM AUTO_INCREMENT=2401 DEFAULT CHARSET=latin1

CREATE TABLE `users` (
  `id` int(20) NOT NULL auto_increment,
  `name` varchar(150) NOT NULL default '',
  `siteid` int(20) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `siteid` (`siteid`)
) ENGINE=MyISAM AUTO_INCREMENT=2401 DEFAULT CHARSET=latin1

CREATE TABLE `hints` (
  `id` int(20) NOT NULL auto_increment,
  `name` varchar(150) NOT NULL default '',
  `siteid` int(20) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `siteid` (`siteid`)
) ENGINE=MyISAM AUTO_INCREMENT=2401 DEFAULT CHARSET=latin1

CREATE TABLE `sections` (
  `id` int(20) NOT NULL auto_increment,
  `name` varchar(150) NOT NULL default '',
  `siteid` int(20) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `siteid` (`siteid`)
) ENGINE=MyISAM AUTO_INCREMENT=2401 DEFAULT CHARSET=latin1

*.siteid corresponderen met sites.id

Nu had ik een query gemaakt, om een idee te geven wat ik wil

SQL:

SELECT t1.sn,t1.ln,t1.gname,t1.uname,t1.sname,t1.hname
FROM (
    SELECT sites.ln as ln
        , sites.sn as sn
        , group_concat(groups.name) as gname
        , group_concat(users.name) as uname
        , group_concat(hints.name) as hname
        , group_concat(sections.name) as sname
        FROM sites
        JOIN groups ON sites.id = groups.siteid
        JOIN users ON sites.id = users.siteid
        JOIN hints ON sites.id = hunts.siteid
        JOIN sections ON sites.id = sections.siteid
        GROUP by sites.sn
    )
    AS t1
WHERE 
  t1.sname LIKE '%GROUP%'
  AND t1.sname LIKE '%SECTION%'
  AND t1.hname NOT LIKE '%HINT%'
;

Alleen die group_concat in combinatie met joins geeft een enorme teringzooi, met heel veel dubbele hits in de t1.*name kolommen

Hoe kan ik dit omzetten dat dit toch gebruikt kan worden?
Alles na WHERE zou ik graag dynamisch willen maken en de rest zeg maar statisch.

Is dit mogelijk en hoe zou ik dit moeten doen?
Alvast bedankt voor de hulp

[NMe]

Je gebruikt GROUP BY ook niet op een manier waarop het gebruikt hoort te worden. Elke database engine met uitzondering van MySQL geeft daar een dikke error op. Zie Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk?

Verder denk ik dat je een probleem op probeert te lossen dat niet bestaat. Kun je eens wat voorbeelddata geven en aangeven hoe/in welke vorm je dat uit je database wil trekken?

Daarnaast: je groups, users, hints en sections tabellen hebben allemaal dezelfde autoincrement. Dat klinkt nogal als een één op één relatie. Waarom heb je dat uitgesplitst in verschillende tabellen?

[_js_]

Een primary key is al een unique key, doet hoeft niet dubbel.

Een join voegt een set toe aan een al bestaande set. Als je een site hebt en je joint twee groups dan heb je twee rijen voor dezelfde site met een andere group. Join je nu twee users op basis van sites dan krijg je in totaal 4 rijen (1 voor elke mogelijke combinatie van site, group en user).

Als je filtert op naam dan zou je ook keys moeten hebben op naam.

Als je filtert op de resultaten van een functie is er weer geen key te gebruiken, en dat zou je dus ook niet moeten doen.

Het opmaken van gegevens (zoals de resultaten in één lange string zetten) is iets wat je eigenlijk pas in je programma zou moeten doen, en niet al in de database.

De voorbeeld query maakt niet precies duidelijk wat je wilt. Wil je de site hebben die aan een bepaalde voorwaarde voldoet? Wil je alle niet gefilterde mogelijkheden uitlezen? Of iets anders?

[Cameron Diaz]

NME:
Hoe zou ik GROUP BY wel moeten gebruiken?
Bij group_concat was een 'GROUP BY' nodig, anders wilde de query niet draaien.

Deze tabellen zijn nog in testfase, ze bevatten 4 keer dezelfde data.
Later zullen deze tabellen verschillende informatie gaan bevatten.

Ik heb trouwens al wat verbetering gebracht in de 'teringzooi' wat de group_concat me gaf. Ik moest overal distinct gebruiken om alle dubbele hits eruit te krijgen, dom dat ik daar niet eerder aan gedacht heb, werd namelijk knetter van al die dubbele hits en wist niet hoe ik ze weg moest krijgen en dat was eigenlijk mijn grootste probleem.

De query is nu

SQL:

SELECT t1.sn,t1.ln,t1.gname,t1.uname,t1.sname,t1.hname
FROM (
    SELECT sites.ln as ln
        , sites.sn as sn
        , group_concat(distinct groups.name) as gname
        , group_concat(distinct users.name) as uname
        , group_concat(distinct hints.name) as hname
        , group_concat(distinct sections.name) as sname
        FROM sites
        JOIN groups ON sites.id = groups.siteid
        JOIN users ON sites.id = users.siteid
        JOIN hints ON sites.id = hunts.siteid
        JOIN sections ON sites.id = sections.siteid
        GROUP by sites.sn
    )
    AS t1
WHERE 
  t1.sname LIKE '%GROUP%'
  AND t1.sname LIKE '%SECTION%'
  AND t1.hname NOT LIKE '%HINT%'
;

[NMe]

Je gebruikt nog steeds GROUP BY verkeerd. Lees die link die ik je eerder gaf eerst eens door.

Je doet het sowieso verkeerd om. Je hebt besloten dat je group_concat nodig hebt en dús gebruik je een group by? Draai het eens om en bedenk jezelf wat het eigenlijk is dat je op wil halen. Zoals ik eerder ook al vroeg: wat voor data heb je en hoe wil je die terugkrijgen?

Als laatste: als die vier tabellen allemaal dezelfde data hebben...hebben (en houden) ze dan ook dezelfde velden? In dat geval, nogmaals: waarom maak je er niet één tabel van met een apart type-veldje?

[cariolive23]

Bij group_concat was een 'GROUP BY' nodig, anders wilde de query niet draaien.

Het draaien van een query is geen doel op zich, daar ga je dus ook nooit pleisters plakken om toch maar "iets" uit de database te krijgen. Het doel is (naar mijn bescheiden mening) om correcte resultaten op te vragen uit de database.

1) Ga MySQL zo configureren dat onzin-queries (waar MySQL gek op is) keihard worden afgeschoten. Dat kan eventueel per connectie, wel zo handig wanneer je de basisconfiguratie niet mag aanpassen. Dit is wel een risico, vergeet dit één keer en je bent weer terug bij af.
2) Met een correcte configuratie kun je een correcte query gaan opstellen die de gewenste resultaten opvraagt. En ga je eerst afvragen wat je nu nodig hebt, dat een functie bestaat wil nog niet zeggen dat je hem moet gebruiken.

[plagvreugd]

Zoals je hopelijk in de link van NMe hebt gelezen, gebruik je GROUP BY in combinatie met aggregatie-functies. Group_concat() is een aggregate-functie, vandaar dat de query 'anders niet runt'.

Ik gok, maar weet niet helemaal wat je wil, dat er (minstens) twee dingen mis zijn met je query.

1. Quote uit de SQL-FAQ:

Zodra je een GROUP BY-clause gebruikt, moeten alle geselecteerde kolommen terugkomen in de GROUP BY list ofwel een aggregate functie hebben.

In jouw SQL gaat dit niet op, ik vermmoed dat je de non-aggregate-kolom die mist in de group by daar wil toevoegen.

2. Als je dit oplost zit je mogelijk nog met een ander punt: ik begrijp uit jouw SQL dat je de groups, users, etc wil weten voor elke combinatie van sites.sn en sites.ln. In de tabel sites is id echter uniek, ik vermoed dat gewenst is om de groups en users en zo weer te geven voor elke site-id. Oplossing hiervoor is om ook sites.id toe te voegen aan de GROUP BY, ook al staat hij niet in de select. In Oracle-SQL is dit iig mogelijk, ik vermoed in MySQL, waar je zoals genoemd idd onzinqueries kan schrijven, ook.

[Cameron Diaz]

Allemaal, bedankt voor alle hulp en ik heb wat aanpassingen gedaan en begrijp nu ook beter hoe ik MySQL moet gebruiken. Ik had geen idee van het feit dat mysql zoveel dingen toelaat die eigenlijk fout zijn.

Ik heb de MySQL sessie ingesteld met het volgende commando en daarna wat commando's geprobeerd met search.
De code om in de sessie te testen of je code geldig is:

SQL:

SET SESSION sql_mode = 'TRADITIONAL,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO';

Ik moest inderdaad sites.ln nog toevoegen aan de GROUP BY en nu werkt het goed.

SQL:

SELECT t1.sn,t1.ln,t1.gname,t1.uname,t1.sname,t1.hname
FROM (
    SELECT sites.ln as ln
        , sites.sn as sn
        , group_concat(distinct groups.name) as gname
        , group_concat(distinct users.name) as uname
        , group_concat(distinct hints.name) as hname
        , group_concat(distinct sections.name) as sname
        FROM sites
        JOIN groups ON sites.id = groups.siteid
        JOIN users ON sites.id = users.siteid
        JOIN hints ON sites.id = hunts.siteid
        JOIN sections ON sites.id = sections.siteid
        GROUP by sites.sn,sites.ln,sites.id
    )
    AS t1
WHERE 
  t1.sname LIKE '%GROUP%'
  AND t1.sname LIKE '%SECTION%'
  AND t1.hname NOT LIKE '%HINT%'
;

Ik krijg nu als uitkomst iets als

sn	ln	gname	uname	sname	hname
BL	Black Light	Groep1,Groep3,Groep4	User1,User3,User5	Section2,Section4,Section7	Hints1,Hints3,Hints6,Hints8

Daarna kan ik in de 'WHERE'-clause zoeken. Bijvoorbeeld dat in t1.gname "Groep3" voorkomt, dat in t1.sname "Section2" voorkomt en dat in t1.hname "Hint3" NIET mag voorkomen, op deze manier is dat mogelijk.

Ik hoop dat nu duidelijk is wat ik probeerde te doen.

[NMe]

Cameron Diaz schreef op donderdag 30 juli 2009 @ 02:43:
Ik hoop dat nu duidelijk is wat ik probeerde te doen.

Ik denk het wel ja. Het is nu dus opgelost?

[Janoz]

Nou, nog lang niet denk ik (maar dat wist je zelf ook wel NMe ).

Wat nu als Hint3 niet voor mag komen maar Hint30 wel?

[NMe]

Janoz schreef op donderdag 30 juli 2009 @ 12:26:
(maar dat wist je zelf ook wel NMe ).

Subtiele hints zijn koel.

[Verwijderd]

Jammergenoeg leest de TS over de echte hint van NMe, normaliseren. En de echte bruikbaarheid van group_concat ligt volgens mij ergens anders.

[edit]
Nu pas begrijp ik de reactie van Janoz. Precies gelijk aan de mijne, maar veel subtieler

[ Voor 26% gewijzigd door Verwijderd op 30-07-2009 13:01 ]

[plagvreugd]

Helaas ben ik niet zo subtiel aangelegd. Of ik ben gewoon een onnozele hals. Of allebei. Behalve dat regel 18 uit TS' script gewijzigd zou moeten worden in

SQL:

  t1.gname LIKE '%GROUP%' 

en dat het m.i: niet nodig is om te werken met een subselect (de WHERE-clauses kunnen gewoon voor de GROUP BY), zie ik geen faliekante missers.

Trouwens, de boel is nu juist toch genormaliseerd, dus in verschillende tabelletjes gestopt? NMe geeft als suggestie toch juist denormaliseren, het weer in 1 tabel flikkeren?

En ik snap niet echt waarom NMe denkt dat er mssn een 1-op-1-relatie tussen de tabellen is. Wat ik ervan begrijp is toch een centrale sites-tabel met 1-op-n-relaties naar groups, users, hints en sections.

Group_concat zou wsl ook niet mijn gekozen manier zijn om de gewenste gegevens op te vragen, maar dat terzijde.

[NMe]

plagvreugd schreef op donderdag 30 juli 2009 @ 16:52:
Helaas ben ik niet zo subtiel aangelegd. Of ik ben gewoon een onnozele hals. Of allebei.

Laten we het anders zeggen: stel "hond" mag niet, maar "zeehond" wel. Zie je hem nu?

Trouwens, de boel is nu juist toch genormaliseerd, dus in verschillende tabelletjes gestopt? NMe geeft als suggestie toch juist denormaliseren, het weer in 1 tabel flikkeren?

Normaliseren betekent niet dat je alles maar in een eigen tabel moet zetten hoor. Eén van de normalisatiestappen is juist dat je bij elkaar zet wat bij elkaar hoort. Exact gelijke datastructuren die maar op één veldje verschillen van elkaar stop je in één tabel met een type-veldje of iets dergelijks.

En ik snap niet echt waarom NMe denkt dat er mssn een 1-op-1-relatie tussen de tabellen is. Wat ik ervan begrijp is toch een centrale sites-tabel met 1-op-n-relaties naar groups, users, hints en sections.

Alle autoincrements hebben dezelfde waarde. Er bestaan dus in elke tabel evenveel records. Dat kun je (bijna) alleen maar bereiken door middel van een één-op-één relatie.

[plagvreugd]

Ja het Hint3-Hint30-voorbeeld van Janoz begreep ik ook, ik zat/zit alleen met het normaliseerverhaal.

@NMe: is wat je beschrijft niet eerder een (algemene) optimalisatiestap ipv een normalisatiestap?

Mooi, nu heb ik meteen geleerd wat dat autoincrement inhoudt, dus snap ik de redenering . Mijn gok is trouwens dat de tabellen evenveel records bevatten omdat TS ze botweg met testdata heeft gevuld.

[Cameron Diaz]

Jullie hebben inderdaad gelijk wat betreft die Hint3 - Hint30. Hij vindt ze nu beiden.
Wat is volgens jullie de 'juiste' manier om de data te verkrijgen die ik zoek?

Want het is inderdaad vervelend dat het er nu zo uit ziet.
Ik heb het nu tijdelijk opgelost met een regexp, maar dat is waarschijnlijk ook niet de meest nette manier

Bedankt weer voor de goede suggesties.

Mijn addon voor de WHERE is nu (erg lelijk, ik weet het)

SQL:

WHERE t1.hname regexp '\^$text,\|,$text,\|,$text\$\|\^$text\$ '

Nu pakt hij alleen het 'hele woord'.

[plagvreugd]

ik zou niet de group_concat hebben gebruikt, al is het maar omdat ik geen MySQL-achtergrond heb en die group_concat dus niet kende

Ik zou zoiets hebben gemaakt. Eerst het juiste site-id zoeken (in de subselect) en dan alle records met dat site-id erbij halen. Maar mogelijk performt dit voor geen meter.

SQL:

SELECT selected.sn, selected.ln, groups.name, users.name, hints.name, sections.name 
FROM (
    SELECT sites.id, sites.sn, sites.ln
        FROM sites
    INNER JOIN groups gr_selected ON sites.id = groups.siteid
    INNER JOIN users us_selected ON sites.id = users.siteid
    INNER JOIN hints hi_selected ON sites.id = hunts.siteid
    INNER JOIN sections se_selected ON sites.id = sections.siteid
    WHERE 
     gr_selected.gname = 'GROUP'
    AND se_selected.sname LIKE 'SECTION'
    AND hi_selected.hname <> 'HINT'
      ) selected
LEFT JOIN groups
    ON selected.id = groups.siteid
LEFT JOIN users 
    ON selected.id = users.siteid
LEFT JOIN hints 
    ON selected.id = hints.siteid
LEFT JOIN sections 
    ON selected.id = sections.siteid
;

Maar als je verder wilt gaan op de ingeslagen weg en een quick&very dirty-oplossing niet erg vindt:
1. Gooi een extra komma-seperator aan het begin en eind van hname
2. Check dat hname niet gelijk is aan %komma-HINT-komma%.

@1: Wijzig regel 7 in je oorspronkelijke query in:

SQL:

        , concat(',',group_concat(hints.name),',') as hname 

@2: Wijzig regel 20 in je oorspronkelijke query in:

SQL:

  AND t1.hname NOT LIKE '%,&HINT,%'   

Ik weet echter niet hoe je die hintparameter invult voor het runnen van de query...

[Cameron Diaz]

Plagveugd, die concat met komma's is geniaal
En op zich is die query van jou netter, alleen wil ik mijn query dus vanuit een script aan kunnen roepen.

Bijvoorbeeld

code:

search 'hints hint3 sections section2 users NOT user1'

Ik kan dus alles voor WHERE als template gebruiken en daarna de regels erna aanmaken naar aanleiding van de argumenten die meegegeven worden aan het script.
Daarom had ik die group_concat ingezet.

Volgens mij heb ik nu precies wat ik nodig heb.
Ontzettend bedankt voor de hulp allemaal.

[NMe]

Je hebt nu alleen het probleem verplaatst. Eerst mocht hint3 niet en hint30 wel, maar wat als hint3 niet mag, maar hint3,0 wel? Heb je weer precies hetzelfde probleem. Bovendien zegt plagvreugd niet voor niks dat het een hele vieze oplossing is.

[plagvreugd]

Alternatief: kies een andere string in de group_concat (en de concat) als separator, van mijn part '*&^%$£"!!' of zo. Nog steeds niet 100% fail safe en t wordt er gezellig nog lelijker op

[MueR]

Doorgaans als ik wil zoeken of 'Hint3' bij een site beschikbaar is, ga ik in de hints tabel zoeken naar hint3, zonder eerst alle hints voor site X op te halen en lastig te gaan doen met concats, regex searches en string splitting. Maar goed, misschien ben ik gewoon gek.

Wat je nu met je group concats aan het doen bent is (misschien wat slecht) genormaliseerde data denormaliseren, om het vervolgens alsnog weer uit elkaar te trekken om op 1 waarde te zoeken. Je bent in je queries je database structuur weer overhoop aan het trekken. Mijn grote vraag: BUT WHY?!

[NMe]

Het enige goeie alternatief is de database goed normaliseren en fatsoenlijk joinen zonder rare group_concats. Dat blijft ook mooi performant als je het goed doet en zou ook niet zo'n gekke hacks nodig hebben.

[pedorus]

Cameron Diaz schreef op vrijdag 31 juli 2009 @ 18:44:
Volgens mij heb ik nu precies wat ik nodig heb.

Over een tijdje bedenk je je dat je daar het woordje 'niet' had vergeten.

MueR schreef op zaterdag 01 augustus 2009 @ 00:03:
Doorgaans als ik wil zoeken of 'Hint3' bij een site beschikbaar is, ga ik in de hints tabel zoeken naar hint3, zonder eerst alle hints voor site X op te halen en lastig te gaan doen met concats, regex searches en string splitting. Maar goed, misschien ben ik gewoon gek.

Maak je geen zorgen, ten minste, niet in dit opzicht Wat ik hier in dit draadje zie is werkelijk een van de meest beroerde methoden om dit op te lossen. Kan volgens mij rechtstreeks naar thedailywtf als (het zoeken in) de productiedatabase opeens steeds trager gaat... GROUP_CONCAT is een recept voor trage queries, afgekapte resultaten en andere gekke problemen. Gelukkig is er genoeg ruimte voor verbetering

Ik zou zeggen, Cameron Diaz, kijk eens naar dingen als EXISTS, IN of INNER JOIN/LEFT JOIN ... IS NULL. Die trouwens vaak automatisch worden geoptimaliseerd, waardoor het in veel gevallen niet uitmaakt welke methode je kiest.
Jouw voorbeeldje:

code:

search 'hints hint3 sections section2 users NOT user1'

is dus bijvoorbeeld iets als:

SQL:

select ... from sites where 
    (id in (select siteid from hints where name="hint3")) and 
    (id in (select siteid from sections where name="section2")) and 
    (id NOT in (select siteid from users where name="user1"))

En als je echt heel graag een group_concat terug wil hebben, dan kun je dat natuurlijk evengoed doen zonder in het resultaat daarvan te gaan zoeken.

NMe schreef op zaterdag 01 augustus 2009 @ 01:53:
Het enige goeie alternatief is de database goed normaliseren [...]

Ik zie eigenlijk niet helemaal het probleem met het datamodel? Behalve dan kleinigheden zoals dat een naam als "id" beroerd is, bijvoorbeeld ivm natural joins die gelijk niet kunnen, en "sn" en "ln" zijn sowieso niet begrijpbaar.
Hoe al die AUTO_INCREMENT=2401's in de TS komen is mij een raadsel, want het voorbeeld geeft weer geen 1-op-1-relatie aan. Extreem toeval