[MySQL] Performance met binaire flags

[djc]

Ik heb voor een van mijn projecten een kleine tabel (MyISAM, 704 rijen, 120KB) waarin ik onder andere een kolom (INT(10) UNSIGNED NOT NULL) heb. Hierin heb ik bits gebruikt om 9 onafhankelijke kwalificaties op te slaan, dus van 1 tot en met 512. Bij de implementatie heb ik ervoor gekozen om van meestgebruikt naar minstgebruikt te gaan, dus 1 komt het vaakst voor, daarna 2 en als laatste 512 (ongeveer). Nu blijkt echter dat zoeken op met name de eerste bit vrij langzaam is.

Ik gebruik als query een WHERE flags & 1. Toen ik merkte dat het niet bijzonder snel was (hij moet bij deze query iets van 300 rijen ophalen) heb ik uiteraard een INDEX op het veld gezet. Ik ben echter nog steeds niet erg onder de indruk van de performance.

Zijn er manieren om dit wat sneller te doen verlopen? Heeft iemand hier ervaring mee? Zou het wellicht helpen om de meestgebruikte juist een van de hogere bits te geven (omdat de index dan misschien effectiever gebruikt wordt)? Tips zijn welkom.

[NMe]

Eerst even de overduidelijke vraag: waarom 9 kenmerken bij elkaar proppen in één veld? Waarom niet gewoon 9 losse velden met elk één kenmerk?

[djc]

-NMe- schreef op zaterdag 18 februari 2006 @ 14:28:
Eerst even de overduidelijke vraag: waarom 9 kenmerken bij elkaar proppen in één veld? Waarom niet gewoon 9 losse velden met elk één kenmerk?

Dan krijg je zoveel kolommen. Plus dat het meer ruimte in beslag neemt? Ik vond dit wel een mooie compacte oplossing.

[BestTested!]

Manuzhai schreef op zaterdag 18 februari 2006 @ 14:44:
[...]
Dan krijg je zoveel kolommen. Plus dat het meer ruimte in beslag neemt? Ik vond dit wel een mooie compacte oplossing.

Och.. wel 8 kolommen meer, daar zeikt menig DB niet over. En als je ze allemaal als bit defineert, zal het met de ruimte ook wel meevallen, klein beetje extra overhead misschien.

[djc]

BestTested! schreef op zaterdag 18 februari 2006 @ 14:59:
Och.. wel 8 kolommen meer, daar zeikt menig DB niet over. En als je ze allemaal als bit defineert, zal het met de ruimte ook wel meevallen, klein beetje extra overhead misschien.

Tsja, misschien is dat dan wel een beter idee. En ik vond het nog wel zo'n elegante oplossing!

[crisp]

Volghens mij is een SET hiervoor bedoelt

[djc]

crisp schreef op zaterdag 18 februari 2006 @ 15:23:
Volghens mij is een SET hiervoor bedoelt

Goed punt! Dat is wat ik zoek.

[crisp]

Ik weet alleen niet in hoeverre MySQL bij een SET wel in staat is eea te indexeren en of FIND_IN_SET een betere performance biedt tov een binary AND (wat met een SET ook nog mogelijk is).

[BestTested!]

A SET is a string object that can have zero or more values

Het is dan wel meteen een string. Je gaat er dan in ieder geval wel op achteruit qua ruimte

[djc]

BestTested! schreef op zaterdag 18 februari 2006 @ 15:50:
Het is dan wel meteen een string. Je gaat er dan in ieder geval wel op achteruit qua ruimte

Uh, volgens mij niet? De "UI" is een string, maar onderliggend gebruikt hij volgens mij gewoon bits.

[Soultaker]

Volgens mij is die SET eigenlijk equivalent aan de constructie met INT die de TS nu al zelf in elkaar gehacked heeft. De SET is wat inzichtelijker, maar natuurlijk minder portable.

In ieder geval denk ik (net als crisp dus al zei) dat SET niet anders performt. Als je veelvuldig queryt en het aantal flags is beperkt, dan kun je er toch echt beter kolommen van maken met een index. En ja, dat kost wel wat extra ruimte; niet zozeer vanwege de kolommen maar vooral vanwege de extra indices.

[djluc]

Eventueel kan je ook kiezen voor een koppeltabel. Dan krijg je dus iets als:

table personen
1=jan
2=piet

table koppel
persoonid=1, kwalificatieid=3
persoonid=1, kwalificatieid=2
persoonid=2 kwalificatieid=3

table kwalificaties
1=goed
2=slecht
3=wow!

[djc]

Soultaker schreef op zaterdag 18 februari 2006 @ 19:07:
Volgens mij is die SET eigenlijk equivalent aan de constructie met INT die de TS nu al zelf in elkaar gehacked heeft. De SET is wat inzichtelijker, maar natuurlijk minder portable.

In ieder geval denk ik (net als crisp dus al zei) dat SET niet anders performt. Als je veelvuldig queryt en het aantal flags is beperkt, dan kun je er toch echt beter kolommen van maken met een index. En ja, dat kost wel wat extra ruimte; niet zozeer vanwege de kolommen maar vooral vanwege de extra indices.

Je hebt volledig gelijk. Een korte benchmark heeft uitgewezen dat gewone flags & 1 tegenover FIND_IN_SET('H', flags) zodanig hetzelfde presteren dat het erop lijkt dat ze precies dezelfde onderliggende strategie gebruiken. Nu is de interface van SET misschien iets netter, maar ik weet niet of dat het nou waard is mijn code weer om te gooien. Meer kolommen zou natuurlijk wel kunnen, maar dat vind ik toch een beetje een 'lelijke' oplossing.

Overigens krijg ik het idee dat de performance-bottleneck bij het queryen misschien niet ligt bij het selecteren maar bij het doorgeven van de grotere hoeveelheid data. Als ik test met een andere flag (die 60 rijen teruggeeft in plaats van 300) is de vertraging significant lager (zeker als ik vergelijk met de added lag die je krijgt doordat de webserver die data moet doorpompen). Dus misschien is de I/O hier meer de bottleneck dan de selectie-strategie van de database.

[ Voor 5% gewijzigd door djc op 18-02-2006 21:11 ]

[djc]

djluc schreef op zaterdag 18 februari 2006 @ 21:00:
Eventueel kan je ook kiezen voor een koppeltabel. Dan krijg je dus iets als:

table personen
1=jan
2=piet

table koppel
persoonid=1, kwalificatieid=3
persoonid=1, kwalificatieid=2
persoonid=2 kwalificatieid=3

table kwalificaties
1=goed
2=slecht
3=wow!

Dat zou betekenen dat ik naast de extra join ook een extra GROUP BY moet uitvoeren, eventueel met een CONCAT_WS() of een SUM(). Ik denk dat mijn performance daar niet beter van wordt, nog afgezien van dat ik het designtechnisch minder mooi vind (nu heb ik al die gegevens keurig samengevoegd tot een veldje dat in 1 rij past).