[MySQL] DB design: 200 checkboxes opslaan, te weinig indexes

[TheBorg]

Ik heb een form met een paar honderd checkboxes. Deze wil ik allemaal opslaan in een database met een MyISAM table (ENUM). Nou is dat niet zo'n probleem, maar ik wil graag ook zoeken in de database waardoor ik tegen het probleem aanloop dat er maar maximaal 64 indexes gemaakt kunnen worden.
Ik kan wel meerdere tabellen maken om 1 form op te slaan, maar dat maakt het geheel natuurlijk ook niet veel overzichtelijker...

Is het mogelijk om al die checkboxes in één tabel op te slaan en ook nog een goede performance te hebben tijdens het zoeken?

[whoami]

Een checkbox opslaan ?
Een form opslaan ?

Je gaat toch de gegevens opslaan ?
Welke gegevens wil je opslaan ? Volgens mij zit je gewoon met een slecht data-model; lees eens een en ander over normaliseren; je gaat nl. niet zomaar alles in één of meerdere tabellen gooien, maar je moet dat doordacht doen.

[MrBucket]

Kan je niet al je checkboxes als bitfields opslaan?
Dat betekent dat je geen tabel hebt van 200 kolommen, maar slechts van een stuk of 8 int-kolommen, waarbij elke bit een bepaalde betekenis heeft. Ik denk dat dit een stuk beter performt dan 200 aparte velden met weet ik hoeveel indexen.

Je moet dan alleen wel met je queries gaan bitneuken (dus zoiets als WHERE (KlantAttrib AND 32) = 1, ofzo), dus expliciete veldnamen ben je dan kwijt.

[whoami]

MrBucket schreef op zaterdag 17 september 2005 @ 16:46:
Kan je niet al je checkboxes als bitfields opslaan?

Mja, dat is wel lekker onderhoudbaar....

[TheBorg]

@whoami:
Ik wil weten of iemand een bepaalde optie heeft aangevinkt (de checkbox) en dit wil ik graag onthouden. Dit kan dan opgeslagen worden als bijv. ENUM('0', '1').

@MrBucket:
Ik had daar ook aan gedacht, maar dan moet er zoveel gebitneukt worden dat ik waarschijnlijk hoofdpijn krijg. De checkboxes zijn wel te groeperen waardoor het mogelijk word om bijv. '1010110' op te slaan en te zoeken met 'WHERE LIKE '?01??10' ofzo. Maar dan freaken de normalisatie helden helemaal door.

[zwippie]

Geen indexes leggen betekent natuurlijk niet dat je dan niet meer kunt zoeken.
Maar misschien kun je alleen de meest relevante checkbox-columns indexeren (als die er zijn natuurlijk).

[SeatRider]

whoami schreef op zaterdag 17 september 2005 @ 16:49:
[...]

Mja, dat is wel lekker onderhoudbaar....

Lijkt me nogal meevallen. Als je nou constantes maakt en een paar kleine functietjes gebruikt performeert het als een trein en kan je opvolger het ook nog begrijpen.

[RobIII]

whoami zei het al: Normaliseren is het keywoord hier.
Kwestie van koppeltabelletje enzo:

Voorbeeldje:

Tabel Klanten
kl_id
kl_naam
kl_adres
...etc

Tabel Opties
opt_id
opt_omschrijving
...

Tabel KlantOpties
kl_id
opt_id

Je kunt nu vrij opties bij maken/verwijderen tot zoveel als je wil zonder steeds aan je tabellen te hoeven komen. Zoeken doe je dan in je tabel KlantOpties waarbij beide kolommen een samengestelde primary key zijn. In dat geval heb je dan één (clustered) index met 2 kolommen (kent MySQL dat? MSSQL wel iig )

Eventueel kun je in de tabel klantopties nog een waarde veld bij maken zodat je 0 of 1 op slaat. Aan de andere kant is dit redelijk onzin: Komt de koppeling klant+optie voor dan is 't automatisch 1, anders 0. Ook zou je hier nog bijvoorbeeld een datum kunnen opslaan wanneer de optie voor het laatst gewijzigd is etc.

Redelijk basic DB ontwerp hoor

Zie overigens ook de SQL FAQ

[ Voor 14% gewijzigd door RobIII op 17-09-2005 17:11 ]

[whoami]

RobIII schreef op zaterdag 17 september 2005 @ 17:04:
Zoeken doe je dan in je tabel KlantOpties waarbij beide kolommen een samengestelde primary key zijn. In dat geval heb je dan één (clustered) index met 2 kolommen (kent MySQL dat? MSSQL wel iig )

Een clustered index (in sql server) is niet hetzelfde als een samengestelde index.
Een clustered index bepaalt de opslagvolgorde van de records in je tabel, en is dus goed als je wilt zoeken op ranges.
Echter, als je een clustered index hebt op een veld dat veel wijzigt, verandert ook je opslagvolgorde -> veel overhead.
Een clustered index zou ik dus enkel leggen op velden die 'sequentieel' toenemen, en nooit / zelden wijzigen.

[RobIII]

whoami schreef op zaterdag 17 september 2005 @ 17:15:
[...]

Een clustered index (in sql server) is niet hetzelfde als een samengestelde index.
Een clustered index bepaalt de opslagvolgorde van de records in je tabel, en is dus goed als je wilt zoeken op ranges.
Echter, als je een clustered index hebt op een veld dat veel wijzigt, verandert ook je opslagvolgorde -> veel overhead.
Een clustered index zou ik dus enkel leggen op velden die 'sequentieel' toenemen, en nooit / zelden wijzigen.

Vandaar dat ik clustered tussen haakjes zette

[MrBucket]

RobIII schreef op zaterdag 17 september 2005 @ 17:04:
whoami zei het al: Normaliseren is het keywoord hier.

Ik ging er (impliciet) vanuit dat er niks meer te normaliseren viel, d.w.z. dat elk record alle 200 ja/nee eigenschappen heeft, en dat er geen afhankelijkheden bestaan tussen deze eigenschappen.

Is dat ook zo, TS?
Of kan het bijvoorbeeld zo zijn dat, als er 1 eigenschap false is, dat er dan 10 andere eigenschappen per definitie 'false' of overbodig zijn? Zo ja, dan kan je die 10 eigenschappen in een aparte tabel onderbrengen, en er met een foreign key naar refereren.

Je krijgt dan een soort sterschema, zoals je ook wel ziet in OLAP-toepassingen.

[TheBorg]

De checkboxes hebben geen relevantie. Ik ben ook helemaal voor de 'normale' methode zoals RobIII aangeeft, maar ik ben nogal bang voor de performance als je 200.000.000 records in je 'KlantOpties' tabel hebt (er is maar 1 index i.p.v. meerdere kleine).

[MrBucket]

Toch is het misschien geen gek idee om dimensies af te splitsen, d.w.z. om gerelateerde eigenschappen in aparte tabellen onder te brengen en vanuit de 'hoofdtabel' ernaar te refereren. Dan krijg je in plaats van:

tblEigenschappen
eigID
eigLustAppels
eigLustPeren
eigLustDruiven
eigLustBananen
eigAllergischVoorKleurstof
eigAllergischVoorStofA
eigAllergischVoorStofB
eigHoudtVanFruit
eigEetVoornamelijkHollands
(...)

nu zoiets:
tblEigenschappen
eigID
eigLustID<---FK
eigAllergieID<---FK
eigHoudtVanFruit
eigEetVoornamelijkHollands

tblLust
lusID
lusAppels
lusPeren
lusDruiven
lusBananen

tblAllergie
allID
allKleurstof
allVoorStofA
allVoorStofB

Je kunt nu op zowel de eigenschap-tabel als de afgesplitste dimensies indexen zetten, wat beter moet performen. Alleen betekent dit met het aanmaken van nieuwe records in je eigenschap-tabel dat je dan wel eerst de juiste FK's uit je dimensie-tabellen moet opzoeken en/of aanmaken.

Je zoektijd gaat erop vooruit, maar het vullen van de database gaat langzamer.

[ripexx]

TheBorg schreef op zaterdag 17 september 2005 @ 18:41:
De checkboxes hebben geen relevantie. Ik ben ook helemaal voor de 'normale' methode zoals RobIII aangeeft, maar ik ben nogal bang voor de performance als je 200.000.000 records in je 'KlantOpties' tabel hebt (er is maar 1 index i.p.v. meerdere kleine).

Na normalisatie is er nog altijd iets al performance maar dat kan je toch benchen. Daarnaast loop je zelf al tegen het feit aan dat er maar 64 indexen mogelijk zijn. Maar hoe reëel zijn die 200 miljoen opties? Want dan zou je initiële tabel ook al 1 miljoen records groot zijn. Dus vul beide opties eens met data en bench de verschillen.

[TheBorg]

Ik zal morgen een database volduwen om te kijken wat er gebeurd. Ik ben benieuwd.

[JaQ]

misschien is het wijs om voor een parameter_name parameter_value constructie te kiezen, oftewel: 1 tabel met alle mogelijke checkboxen (de namen dus) en 1 tabel waarin je de parameter naam (de checkbox dus) matched aan de parameter_value, uiteraard samen met het gerelateerde gegeven waarvoor deze waarde geldt.

Voorbeeld:

users( id, nog, wat, velden)
parameters(id, naam)
waarden( user_id, parameter_id, waarde)

hier kan je prima op indexeren en zoeken. Op het moment dat je ranzige formulieren bedenkt met 200 checkboxen, dan is een ranzige database oplossing als deze de enige mogelijkheid volgens mij.

[RobIII]

DrFrankenstoner schreef op zondag 18 september 2005 @ 13:44:
misschien is het wijs om voor een parameter_name parameter_value constructie te kiezen, oftewel: 1 tabel met alle mogelijke checkboxen (de namen dus) en 1 tabel waarin je de parameter naam (de checkbox dus) matched aan de parameter_value, uiteraard samen met het gerelateerde gegeven waarvoor deze waarde geldt.

Voorbeeld:

users( id, nog, wat, velden)
parameters(id, naam)
waarden( user_id, parameter_id, waarde)

hier kan je prima op indexeren en zoeken. Op het moment dat je ranzige formulieren bedenkt met 200 checkboxen, dan is een ranzige database oplossing als deze de enige mogelijkheid volgens mij.

Heb je het topic wel gelezen? We dragen hier al exact dezelfde oplossing voor, en dat is geen ranzige oplossing, maar een genormaliseerde oplossing.

[ Voor 4% gewijzigd door RobIII op 18-09-2005 16:28 ]

[Verwijderd]

Het mag dan wel een genormaliseerde oplossing zijn, maar het grote nadeel van deze oplossing is dat elke eigenschap van hetzelfde datatype is. Omdat je kolommen gaat voorstellen dmv. rijen, wordt je code ook wsl. afhankelijk van de inhoud van je database, en niet alleen van de structuur, zoals het in principe zou moeten zijn.

Met andere woorden, de gegeven oplossing is mooi als je niet meer dan een pure opsomming van eigenschappen wilt. Wil je nog meer, zoals query'en, sorteren, zoeken, etc., dan is deze oplossing ook in mijn ogen ranzig.

[ Voor 9% gewijzigd door Verwijderd op 18-09-2005 17:17 ]

[Gomez12]

En als je bang bent voor te veel rows kan je nog altijd gaan nadenken over "default waardes" in je applicatie. Dus alleen een checkbox in het model van robiii opslaan als het true is. Standaard is het false.Je applicatie moet nu alleen wat meer uitzoekwerk doen.

Of idd meerdere tabellen met opties. Dus hoofd opties, subopties, sub-subopties. ( alhoewel dit qua onderhoudbaarheid misschien wel een kleine ramp veroorzaakt.

[ripexx]

Het probeel van de TS is eigenlijk dat hij op elke kolom een index wil leggen? Maar waarom zou je 200 indexen op een tabel gaan leggen? Dan weet je absoluut niet wat je kan verwachten. Dit is via een koppeltabel/normalisatie slag op te lossen. Maar het lijkt er meer op dat je te maken hebt met een of andere datawarehouse oplossing, wat kan ik kan nog steeds niet een fatsoendelijk voorbeeld verzinnen.

[RobIII]

Verwijderd schreef op zondag 18 september 2005 @ 17:15:
Het mag dan wel een genormaliseerde oplossing zijn, maar het grote nadeel van deze oplossing is dat elke eigenschap van hetzelfde datatype is. Omdat je kolommen gaat voorstellen dmv. rijen, wordt je code ook wsl. afhankelijk van de inhoud van je database, en niet alleen van de structuur, zoals het in principe zou moeten zijn.

Met andere woorden, de gegeven oplossing is mooi als je niet meer dan een pure opsomming van eigenschappen wilt. Wil je nog meer, zoals query'en, sorteren, zoeken, etc., dan is deze oplossing ook in mijn ogen ranzig.

De TS heeft het over checkboxes (0 of 1, on/off, ja/nee) en niet over andere datatypes. En dan zijn ze dus allemaal van het zelfde type en dus prima in 1 tabel te frotten en te query-en etc.

[Verwijderd]

RobIII schreef op zondag 18 september 2005 @ 17:35:
[...]

De TS heeft het over checkboxes (0 of 1, on/off, ja/nee) en niet over andere datatypes. En dan zijn ze dus allemaal van het zelfde type en dus prima in 1 tabel te frotten en te query-en etc.

Inderdaad, ik had dat overlezen. In dat geval is het ook in mijn ogen de beste oplossing.

Als de eigenschappen niet allemaal van hetzelfde type zijn, is het een heel ander verhaal (een goede oplossing daarvoor kan ik nu ook niet in 1 2 3 bedenken). Gelukkig is dat niet van toepassing

[TheBorg]

Ik ben nu de database aan het vullen. Duurt denk ik wel een uurtje. Daarna eens wat queries erop los laten.

[JaQ]

RobIII schreef op zondag 18 september 2005 @ 16:27:
[...]


Heb je het topic wel gelezen? We dragen hier al exact dezelfde oplossing voor, en dat is geen ranzige oplossing, maar een genormaliseerde oplossing.

Blijkbaar heb ik het draadje niet goed genoeg gelezen. Genormaliseerd is het inderdaad wel, maar dat maakt het niet minder ranzig (zoals eerder vermeld en dat hoef ik dus niet nogmaals te vertellen ).

Een goed genormaliseerde database ontwerp maakt niet heilig. Wat volgens de normalisatie regels correct is, is niet altijd de beste oplossing qua performance of business layer programmatuur. Iedereen past in de praktijk wel eens een ranzige oplossing toe, maar dat maakt het uit theoretisch oogpunt niet minder fout. Overigens gaat het niet om de kunst van het programmeren of normaliseren, maar om een werkbare oplossing. (en de vraag hierbij blijft of het wijs is om een form met 200 checkboxes te maken)

[RobIII]

DrFrankenstoner schreef op zondag 18 september 2005 @ 20:12:
Blijkbaar heb ik het draadje niet goed genoeg gelezen. Genormaliseerd is het inderdaad wel, maar dat maakt het niet minder ranzig (zoals eerder vermeld en dat hoef ik dus niet nogmaals te vertellen ).

De enige die vermeldt dat het ranzig is ben je zelf (en Kilonmanjera die er later op terug komt), en daar geef je dan niet eens een argument bij? Geef mij eens 1 argument waarom het ranzig is?

DrFrankenstoner schreef op zondag 18 september 2005 @ 20:12:
Een goed genormaliseerde database ontwerp maakt niet heilig.

Dat heeft ook niemand gezegd, maar een DB met een tabel met 200+ kolommen voor wat checkboxes is dat ook niet. Evenals binary fields en dat soort ongein. Weet je wat? We maken een tabel met Memo-fields en knallen er XML in waarin de waardes van de checkboxes worden opgeslagen...

DrFrankenstoner schreef op zondag 18 september 2005 @ 20:12:
Wat volgens de normalisatie regels correct is, is niet altijd de beste oplossing qua performance of business layer programmatuur. Iedereen past in de praktijk wel eens een ranzige oplossing toe, maar dat maakt het uit theoretisch oogpunt niet minder fout.

Nu spreek je jezelf tegen? Iedereen past wel eens een ranzige oplossing toe (omwille van performance of business layer), maar dat maakt het niet minder fout? Waarom doe je het dan niet meteen wél goed? (Tenzij er deadlines in het geding zijn vind ik het zowieso geen reden om "ranzige oplossingen" te gebruiken. Kijk, dat je soms dingen zus_en_zo maakt omdat een andere manier teveel werk is (deadlines) of omdat je kennis niet ver genoeg reikt (op dat moment) of whatever, laat ik even buiten beschouwing. Iedereen krijgt nachtmerries als 'ie code van zichzelf van X aantal jaren geleden onder ogen krijgt.

DrFrankenstoner schreef op zondag 18 september 2005 @ 20:12:
Overigens gaat het niet om de kunst van het programmeren of normaliseren, maar om een werkbare oplossing. (en de vraag hierbij blijft of het wijs is om een form met 200 checkboxes te maken)

Waarom de TS 200 checkboxes heeft of wil heeft hij niet uit de doeken gedaan, en IMHO is dat ook totaal niet interessant. For all I know is het een webpage met 5 duidelijke blokjes met vragen, waarna bij het beantwoorden weer een nieuw blokje uitklapt aan de hand van het antwoord op de vraag etc... en is die 200 checkboxes dus helemaal geen probleem. Anderzijds kan het ook een pagina zijn waarop inderdaad gewoon 200+ checkboxes zijn gepleurd. Of is het wel heel iets anders. Dat doet er niet toe. Hij vraagt om een oplossing voor zijn probleem. Als de TS wilde dat we meedachten over het ontwerp van zijn applicatie dan had hij dat wel gevraagd. En dan heb ik het dus niet over de kunst van het programmeren of normaliseren, maar draag ik juist een werkbare oplossing aan.

[ Voor 5% gewijzigd door RobIII op 18-09-2005 20:26 ]

[TheBorg]

Ik heb er even mee zitten spelen. Dit:

code:

SELECT UserID, count(*) AS pietje FROM test WHERE opt = 10 OR opt = 20 OR opt = 30 GROUP BY UserID ORDER BY pietje DESC

is misschien niet de meest slimme query, maar het duurt 17 seconden.
Onwerkbaar dus.

Ik zit er aan te denken om de checkboxes te nummeren, spares in te bouwen, en op te slaan als '100110110110110011100011etc' en voor een SELECT te gebruiken 'WHERE LIKE '1?10?101?110??10?etc'.

Als je een form wil zien met (bijna) 200 checkboxes moet je hier eens kijken.
Het enige verschil is dat ik een beetje (veel) wil zoeken en manipuleren (online enquêtes bijv., kan ook wat anders zijn, 200 checkboxjes is echt niet veel hoor ).

[ Voor 18% gewijzigd door TheBorg op 18-09-2005 20:35 ]

[RobIII]

TheBorg schreef op zondag 18 september 2005 @ 20:29:
Ik heb er even mee zitten spelen. Dit:

code:

SELECT UserID, count(*) AS pietje FROM test WHERE opt = 10 OR opt = 20 OR opt = 30 GROUP BY UserID ORDER BY pietje DESC

is misschien niet de meest slimme query, maar het duurt 17 seconden.
Onwerkbaar dus.

Ik zit er aan te denken om de checkboxes te nummeren, spares in te bouwen, en op te slaan als '100110110110110011100011etc' en voor een SELECT te gebruiken 'WHERE LIKE '1?10?101?110??10?etc'.

Als je een form wil zien met (bijna) 200 checkboxes moet je hier eens kijken.
Het enige verschil is dat ik een beetje (veel) wil zoeken.

Ik zeg het nog maar 1 keer: De oplossing waar je nu over zit te denken is waardeloos. Zeker als je met LIKE aan de slag gaat, gaat je performance hard naar beneden. Bijvoorbeeld: Indices kunnen al niet meer worden gebruikt als de eerste "bit" onbekend is. Ga dan, als je het toch wilt doen, voor de "echte binaire" oplossing en ga met AND en OR aan de slag op int's. Niet dat dat ideaal is ofzo...

[ Voor 10% gewijzigd door RobIII op 18-09-2005 20:38 ]

[ACM]

TheBorg schreef op zondag 18 september 2005 @ 20:29:
is misschien niet de meest slimme query, maar het duurt 17 seconden.

En had je daarbij ook een index op (enkel) de opt-kolom? Schrijf trouwens voor het leesgemak opt IN (10, 20, 30)

Wat ik me nog afvraag is het doel van die query, je wilt van alle users weten hoeveel van die drie opties ze aangevinkt hadden? En dan alle gebruikers ook nog eens onder elkaar gooien.
Als die query er 17 seconden over deed (zelfs zonder index op opt) betekent dat ofwel dat je een bijzonder slome server, of een grote dataset hebt. Het lijkt me nogal onverstandig om die complete dataset op die manier naar je client te smijten, dus ik neem aan dat het een test is die je verder niet in je applicatie gebruikt?

TheBorg schreef op zondag 18 september 2005 @ 20:29:
Als je een form wil zien met (bijna) 200 checkboxes moet je hier eens kijken.

En dat wordt dus niet in een hele brede, maar een hele diepe tabel opgeslagen. In dit geval met alleen een userid en een optieid, het aanwezig zijn is "true" het afwezig zijn is false (en met een left outer join kan je zowel alles wat true als false is in 1 query ophalen).

[ Voor 26% gewijzigd door ACM op 18-09-2005 21:10 ]

[TheBorg]

ACM schreef op zondag 18 september 2005 @ 21:04:
[...]

En had je daarbij ook een index op (enkel) de opt-kolom? Schrijf trouwens voor het leesgemak opt IN (10, 20, 30)

Wat ik me nog afvraag is het doel van die query, je wilt van alle users weten hoeveel van die drie opties ze aangevinkt hadden? En dan alle gebruikers ook nog eens onder elkaar gooien.
Als die query er 17 seconden over deed (zelfs zonder index op opt) betekent dat ofwel dat je een bijzonder slome server, of een grote dataset hebt. Het lijkt me nogal onverstandig om die complete dataset op die manier naar je client te smijten, dus ik neem aan dat het een test is die je verder niet in je applicatie gebruikt?
[...]

En dat wordt dus niet in een hele brede, maar een hele diepe tabel opgeslagen. In dit geval met alleen een userid en een optieid, het aanwezig zijn is "true" het afwezig zijn is false (en met een left outer join kan je zowel alles wat true als false is in 1 query ophalen).

Ik heb twee indexen, ook een op 'opt'. Had bij wijze van test 50.000.000 records aangemaakt.

Het belangrijkste is eigenlijk dat ik in de DB kan zoeken: Stel je zoekt een candidaat voor een vacature en je hebt de tweakers.net DB ter beschikking, je zoekpagina kan er dan hetzelfde uitzien als de tweakers.net gallery. Het mooiste zou zijn als ik de resultaten zowel 'strict' als fuzzy kan krijgen. Natuurlijk moet het niet te lang duren (met ong. 2 milj. mensen in de DB), want de werkgevers zitten heel de dag op de site te zoeken.

Voor de performance zou een tekstfile ideaal zijn, maar dat is nogal moeilijk te combineren met de rest van de data die al in de database staat.

[ Voor 6% gewijzigd door TheBorg op 18-09-2005 21:39 ]

[ACM]

Sja, met zulke aantallen zal je sowieso naar slimme(re) trucjes moeten kijken als een index niet voldoet. Een 200-kolom brede tabel zal je daar niet bij helpen, want MySQL kan maar 1 index per tabel in een query gebruiken, dus als je dan op 3 booleans wilt zoeken zal MySQL er een moeten gebruiken (tenzij je een gecombineerde index daarvoor had) en aangezien het booleans zijn zou dat een nogal inefficiente index-zoektocht zijn.

Een potentiele verbetering die ik zo kan bedenken ik om de query anders op te zetten, voor strict:

SQL:

SELECT t1.userid FROM tabel t1 
         JOIN tabel t2 ON t1.userid = t2.userid AND t2.opt = 20
         JOIN tabel t3 ON t1.userid = t3.userid AND t3.opt = 30
WHERE t1.opt = 10

Of zoiets:

SQL:

SELECT t1.userid FROM tabel t1 
WHERE t1.opt = 10
AND t1.userid IN (SELECT t2.userid FROM tabel t2 WHERE t2.opt = 20
AND t2.userid IN (...)

Of zelfs spelen met union (intersect kent MySQL uiteraard weer niet )

Bovenstaande queries kunnen sneller zijn, maar hoeven het niet. Zeker in MySQL's zeer beperkte query-planner is het erg lastig goede performance uit dergelijke grote aantallen te krijgen.

Test overigens ook hoelang het duurt om 1 conditie op te halen (dus select userid from tabel where opt = 10), als dat al meerdere seconden kost, dan gaat bovenstaande tabelstructuur niet best werken in termen van performance. Ow en test je query ook nog met een LIMIT-clause erachter, de kans is aanwezig dat je query-tijd dan een stuk lager is, en sowieso is dat een realistischer resultaatset.

Mocht je nog de mogelijkheid hebben over te stappen op een ander DBMS, probeer dan nog met PostgreSQL (8.1 Beta) te kijken hoe dat presteert (die overigens een onbeperkt aantal indexen op een tabel aankan en die ook weet te combineren indien nodig ). In mijn ervaring presteert die met dit soort queries aanzienlijk beter, zij het dat je wel wat meer tuning nodig hebt in het begin en wat actiever onderhoud tijdens het gebruik.

[ Voor 11% gewijzigd door ACM op 18-09-2005 22:28 ]

[ripexx]

TheBorg schreef op zondag 18 september 2005 @ 20:29:
Als je een form wil zien met (bijna) 200 checkboxes moet je hier eens kijken.
Het enige verschil is dat ik een beetje (veel) wil zoeken en manipuleren (online enquêtes bijv., kan ook wat anders zijn, 200 checkboxjes is echt niet veel hoor ).

Dus als er een optie bijkomt kan je dus ook je datamodel aanpassen? Lekker handig. Zie ver ACM's verhaal. Heb je naast de breede variant (200 kolommen) ook de diepe variant geprobeerd?

[TheBorg]

Die query was maar een voorbeeld om te kijken wat er gebeurd als je op 3 checkboxjes zoekt en de resultaten gesorteerd wilt hebben op 'best-match'. Maar dat kunnen ook best 150 checkboxjes zijn.

Een SELECT duurt maar 0.0034 seconden. De tijd die ik aangaf was met LIMIT 0, 30. Dat lijkt me vrij normaal.

Ik heb nog tabel gemaakt met daarin 2 kolommen (userid en opt) en een miljoen records. Opt is een string van 255 tekens met random enen en nullen.

SQL:

SELECT * FROM `test2` WHERE opt LIKE
'____1__0__1__0__1__0__1__0__1__0__1__etc_'

duurt ongeveer een seconde. Misschien gaat het efficiënter als ik de Levenshtein Distance bereken.

@ACM:
Ik kan die queries niet helemaal plaatsen, maar misschien hebben we elkaar niet helemaal begrepen?

Overschakelen naar een ander DBMS wordt een hel. Maar als het niet anders kan dan moet het...

[B-Man]

ACM schreef op zondag 18 september 2005 @ 22:25:
Mocht je nog de mogelijkheid hebben over te stappen op een ander DBMS, probeer dan nog met PostgreSQL (8.1 Beta) te kijken hoe dat presteert (die overigens een onbeperkt aantal indexen op een tabel aankan en die ook weet te combineren indien nodig ). In mijn ervaring presteert die met dit soort queries aanzienlijk beter, zij het dat je wel wat meer tuning nodig hebt in het begin en wat actiever onderhoud tijdens het gebruik.

offtopic:
Uhm, begrijp ik nu goed dat Postgres i.t.t. wat de manual beweert wel meerdere indices kan gebruiker per query? Ik loop nu tegen een beperking in MySQL aan waar ik eigenlijk in een query meerdere indices gebruikt zou willen hebben, om erg veel multi-column indices te voorkomen.

[TheBorg]

Ik kan die enen en nullen nog in elkaar duwen tot binaire data. 256 checkboxen worden dan 32 bytes. 8 bytes voor de UserID. Maakt samen 40 bytes. 40 * 2,000,000 = met wat overhead 100MB. Nogal een schraal getal t.o.v. als ik in de diepte ga (meer dan 4GB) of in de breedte (1200MB). Past ook wat makkelijker in de cache.

De Levenshtein UDF kan dan aangepast worden om de binaire data weer uit elkaar te trekken tot een string met enen en nullen.

[RobIII]

*kuch*
hier stond onzin

[ Voor 91% gewijzigd door RobIII op 19-09-2005 01:13 ]

[ACM]

TheBorg schreef op maandag 19 september 2005 @ 00:30:
@ACM:
Ik kan die queries niet helemaal plaatsen, maar misschien hebben we elkaar niet helemaal begrepen?

't Zijn varianten van "zoek iedereen die aan al deze drie parameters voldoet".

B-Man schreef op maandag 19 september 2005 @ 00:54:

offtopic:
Uhm, begrijp ik nu goed dat Postgres i.t.t. wat de manual beweert wel meerdere indices kan gebruiker per query? Ik loop nu tegen een beperking in MySQL aan waar ik eigenlijk in een query meerdere indices gebruikt zou willen hebben, om erg veel multi-column indices te voorkomen.

Waar in de manual wordt er beweert dat PostgreSQL maar 1 index per query kan gebruiken dan? Hoewel je deels gelijk hebt trouwens, maar bij Postgres hangt het van de query af hoeveel er gebruikt kunnen worden, niet zozeer van beperkingen aan het query-systeem.
Sowieso kan PostgreSQL al jaren twee indexen voor één tabel gebruiken met zoiets:

SQL:

SELECT ... FROM tabel a
WHERE (a.n = n AND a.m = m AND a.o = o)
OR
(a.n = n AND a.m = m AND a.p = p)

Als er een gecombineerde index op "n, m en o" en "n, m en p" is, zullen deze gecombineerd worden. Verder is men in PostgreSQL 8.1 (die dus nog beta is) daar nog weer een stap verder in gegaan met de zgn "bitmap index scan", daarbij kunnen diverse indexen gecombineerd worden zelfs als er geen gecombineerde index was (bijv in bovenstaande voorbeeld een losse index op a.n en een losse op a.m). Dat is waarschijnlijk wel iets trager dan een gecombineerde index gebruiken, maar zo te lezen precies wat jij nodig hebt.

[Verwijderd]

offtopic: runt GoT nu op MySQL of pgSQL? Dat MySQL maar 1 index per query kan gebruiken, wist ik tot hiertoe niet, en dan vraag ik mij ineens af hoe sites zoals GoT (als hij op MySQL runt) efficiënt kan draaien. Er zullen toch wel immers queries met minstens 2 joins gebruikt worden?

[Erkens]

Verwijderd schreef op maandag 19 september 2005 @ 12:40:
offtopic: runt GoT nu op MySQL of pgSQL? Dat MySQL maar 1 index per query kan gebruiken, wist ik tot hiertoe niet, en dan vraag ik mij ineens af hoe sites zoals GoT (als hij op MySQL runt) efficiënt kan draaien. Er zullen toch wel immers queries met minstens 2 joins gebruikt worden?

1 index per tabel per query als ik me niet vergis
verder is het een kwestie van slim je indexen plaatsen om er zoveel mogelijk performance uit te persen.

offtopic:
GoT (React) draait op MySQL trouwens

[B-Man]

offtopic:
ACM (en andere geinteresseerden), ik heb even een apart topic geopend over de index mogelijkheden van Postgres.
TS: excuses voor de topic hijack, we gaan verder in een ander topic.

[jochemd]

ACM schreef op maandag 19 september 2005 @ 07:26:

Sowieso kan PostgreSQL al jaren twee indexen voor één tabel gebruiken met zoiets:

SQL:

SELECT ... FROM tabel a
WHERE (a.n = n AND a.m = m AND a.o = o)
OR
(a.n = n AND a.m = m AND a.p = p)

Als er een gecombineerde index op "n, m en o" en "n, m en p" is, zullen deze gecombineerd worden.

Volgens mij is het precies andersom: niet de indexen worden gecombineerd, de predicaten worden gecombineerd.. Deze query wordt (volgens mij sinds 8.0) gereduceerd tot

SQL:

WHERE a.n = n AND a.m = m AND (a.o = o OR a.p = p)

Vervolgens wordt daar of de index (n, m, o) of de index (n, m, p) voor gebruikt, maar alleen het (n, m) deel. (De keuze welke index wordt gebruikt hangt in de praktijk waarschijnlijk af van hoe oud ze zijn en in welke volgorde ze gevacuumd worden. Beide indexen hebben voor een statische tabel de zelfde selectiviteit voor deze query.)

Meerdere indexen voor één scan kan echt pas sinds PostgreSQL 8.1.

[P_de_B]

Beetje offtopic misschien, maar een index op een checkbox status lijkt me niet selectief genoeg?

[jochemd]

P_de_B schreef op maandag 19 september 2005 @ 16:37:
Beetje offtopic misschien, maar een index op een checkbox status lijkt me niet selectief genoeg?

Absoluut niet offtopic: ik denk dat alle tot op heden aangedragen opties inderdaad vrij kansloos zijn door de gebrekkige selectiviteit. MySQL kan gewoon niets zinnigs met indexen over alleen maar velden met booleans. Onder MySQL is de beste optie waarschijnlijk om alle indexen weg te gooien, de data als bitmaps op te slaan en op die manier maximaal ruimte te besparen. Dan houd je misschien 40 bytes per record over (inclusief overhead en primary key) wat bij 1 miljoen records betekent dat de hele tabel makkelijk in RAM past en je de traditionele I/O bottleneck van de disks omzeilt en gelimiteerd wordt door bus en CPU.
Op die manier zelfbouwen is waarschijnlijk net zo handig als de bitmap indexen van bijvoorbeeld Oracle.

Als je echt iets nuttigs wil met indexen en meerdere predicates per query hebben dan moet je een database nemen die partiële indexen aankan en meerdere indexen per scan. Leg je partiële indexen alleen over die kolommen waar je een fatsoenlijke selectviteit hebt zodat je de planner er ook niet mee opzadelt om 200 equivalente query paden door te rekenen en misschien wordt het wat
Bij mijn weten bestaat een database die dat kan overigens niet (tenzij je beta software acceptabel vindt, dan kan je PostgreSQL 8.1 proberen).