[php/mysql] Bitwise gegevens opslaan

[RSD]

Allereerst vroeg ik me af of het verstandig is om bitwise gegevens op te slaan, volgens mij namelijk wel.

Ik wil het bijvoorbeeld alsvolgt gaan doen:

PHP:

$category = array(1 => "heks",2 => "trol",4 => "ridder");

Het veld category kan dus een combinatie van deze twee bevatten, als deze bijvoorbeeld het getal 3 bevat, dan houdt dit een heks en een trol in (1 + 2 = 3).

Ik kan nu dus bitwise volgens mij snel zoeken op bepaalde combinaties in de database.

Maar als ik nu bijvoorbeeld een categorie selecteer en deze heeft de waarde 3, hoe converteer ik die dan naar een array met een heks en een trol erin. Ik kan hier zelf een functie voor maken, die van "11" de waardes doorloopt en in geval van een 1 de waarde aan een array toevoegt.

Daarnaast als ik meerdere van dit soort velden heb, hoe kan ik dan als beste de indexen plaatsen, als er op elke mogelijke variant gezocht kan worden. Moet ik dan op elk veld een aparte index plaatsen of eentje over het geheel heen?

[.oisyn]

Ik zou gewoon een set gebruiken in de database. Dat is onder water een bitset, alleen is het weggeabstraheerd en communiceer je gewoon met de database alsof het lijstjes zijn.

Maar als ik nu bijvoorbeeld een categorie selecteer en deze heeft de waarde 3, hoe converteer ik die dan naar een array met een heks en een trol erin.

De bits aflopen, en als een bit gezet is dan voeg je dat element toe aan de lijst.

[RSD]

Als ik in een tabel bijvoorbeeld een kolom heb met een id en een kolom met een status (online,offline). Is het dan verstandig om de primary key over id,status te doen als in where veel gebruik wordt gemaakt van where status='online' of heeft dat totaal geen zin?

[Verwijderd]

RSD schreef op maandag 07 maart 2011 @ 17:23:
Als ik in een tabel bijvoorbeeld een kolom heb met een id en een kolom met een status (online,offline). Is het dan verstandig om de primary key over id,status te doen als in where veel gebruik wordt gemaakt van where status='online' of heeft dat totaal geen zin?

Dan worden je keys héél vaak herschreven lijkt mij, maar of de mogelijke snelheidswinst bij het ophalen meeweegt bij het wijzigen zou je moeten testen.

[borft]

dat heeft geen zin,. Als je composite keys gebruikt in een index, worden de keys op volgorde gebruikt.

voorbeeld:
je key is naam-achetrnaam-leeftijd

als je in je query geen naam opgeeft, maar wel een achternaam, dan kan de composite key niet gebruikt worden. Sowieso moeten alle waardes van je primary key uniek zijn. Houd dat in het achterhoofd. Wat het gebruik van indeces, kan ik je aanraden om kolommen die je vaak gebruikt altijd te indexeren, scheelt heel veel tijd.

[.oisyn]

RSD schreef op maandag 07 maart 2011 @ 17:23:
Als ik in een tabel bijvoorbeeld een kolom heb met een id en een kolom met een status (online,offline). Is het dan verstandig om de primary key over id,status te doen als in where veel gebruik wordt gemaakt van where status='online' of heeft dat totaal geen zin?

Sowieso geen primary key, want je id is gewoon je primary key. Als status ook onderdeel is van de primary key dan moet je die status ook meegeven bij foreign keys in andere tabellen naar je huidige tabel.

Je kunt wel een index opnemen, maar een index over (id, status) is alleen van belang als je op beide zoekt. Aangezien id zelf al uniek is (en automatisch geindexeerd omdat het een primary key is) is die index niet echt nuttig. Als je alleen zoekt op status (en niet op id), dan zou je een index over (status) kunnen maken. Wil je naast het zoeken op status ook sorteren op id, dan is een index over (status, id) mogelijk een goede keuze.

Maar of het nuttig is hangt nogal af van hoe vaak je zoekt en hoe vaak je insert.

[Verwijderd]

Op zich kan het gebruik van een bitmask in een tabel best handig zijn, maar alleen als je zeker weet dat het aantal verschillende categorieën beperkt is. De maximale grootte van een integer in MySQL is 2^64 - 1 ( BIGINT UNSIGNED ). Dit betekent dus dat je nooit meer dan 63 categorieën kan hebben.

De mogelijke categorieën zou je op kunnen slaan in een aparte tabel, bijvoorbeeld:

id INT PRIMARY KEY	naam VARCHAR
1	heks
2	trol
4	ridder

Je kan dan als volgt alle spelers, of wat het ook mogen zijn, van een bepaalde category uit de database opvragen met behulp van een bitwise operator:

SQL:

SELECT * FROM player WHERE category & ( SELECT id FROM category WHERE naam = "heks" );

Je kan ook van een bepaalde speler alle categorieën opvragen:

SQL:

SELECT c.naam
FROM category c
INNER JOIN player p ON p.category & c.id
WHERE p.id = <VUL HIER HET PLAYER ID IN>

[ Voor 0% gewijzigd door Verwijderd op 07-03-2011 21:16 . Reden: spelling ]

[.oisyn]

Waarom zo moeilijk doen als je gewoon een SET kunt gebruiken als database type?

[GlowMouse]

.oisyn schreef op maandag 07 maart 2011 @ 23:12:
Waarom zo moeilijk doen als je gewoon een SET kunt gebruiken als database type?

Het toevoegen van een nieuw soort element kost een volledige rebuild van de table _{tenzij je het slim aanpakt en wat risico wilt nemen}.

Verwijderd schreef op maandag 07 maart 2011 @ 21:04:
Op zich kan het gebruik van een bitmask in een tabel best handig zijn, maar alleen als je zeker weet dat het aantal verschillende categorieën beperkt is. De maximale grootte van een integer in MySQL is 2^64 - 1 ( BIGINT UNSIGNED ). Dit betekent dus dat je nooit meer dan 63 categorieën kan hebben.

Jawel, je kunt er ook 64 hebben.

[Verwijderd]

GlowMouse schreef op maandag 07 maart 2011 @ 23:40:
[...]
Jawel, je kunt er ook 64 hebben.

Je hebt gelijk; het zijn er inderdaad 64.
Ik was uitgegaan van het extreme geval waarbij je een speler hebt die tot alle categorieën behoort,
maar die waarde is exact 2⁶⁴ - 1

[.oisyn]

GlowMouse schreef op maandag 07 maart 2011 @ 23:40:
[...]

Het toevoegen van een nieuw soort element kost een volledige rebuild van de table _{tenzij je het slim aanpakt en wat risico wilt nemen}.

Het toevoegen aan het eind meestal niet. De vraag is dus hoe vaak je je tabel aanpast.

[RSD]

De tabel wordt nauwelijks aangevuld. Er wordt wel erg veel gezocht op de verschillende mogelijkheden die een kolom kan hebben. Daarnaast zijn er meerdere kolommen. Is het nu vstandig om per kolom een index te doen of beter 1 index over alle kolommen te samen en dan in elke query op alle kolommen t selecteren.

[GlowMouse]

.oisyn schreef op dinsdag 08 maart 2011 @ 10:51:
[...]

Het toevoegen aan het eind meestal niet. De vraag is dus hoe vaak je je tabel aanpast.

Elke ALTER TABEL-query zorgt voor een rebuild van de table, op het toevoegen van een index aan een moderne InnoDB-tabel na.

RSD schreef op dinsdag 08 maart 2011 @ 20:49:
De tabel wordt nauwelijks aangevuld. Er wordt wel erg veel gezocht op de verschillende mogelijkheden die een kolom kan hebben. Daarnaast zijn er meerdere kolommen. Is het nu vstandig om per kolom een index te doen of beter 1 index over alle kolommen te samen en dan in elke query op alle kolommen t selecteren.

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

[.oisyn]

GlowMouse schreef op dinsdag 08 maart 2011 @ 22:32:
[...]

Elke ALTER TABEL-query zorgt voor een rebuild van de table, op het toevoegen van een index aan een moderne InnoDB-tabel na.

*kuch*
[q=http://dev.mysql.com/doc/refman/5.5/en/alter-table.html]For some operations, a “fast” ALTER TABLE is possible that does not require a temporary table:
[..]
• Changing the definition of an ENUM or SET column by adding new enumeration or set members to the end of the list of valid member values. (Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.)
Overigens had ik het in eerste instantie niet specifiek over MySQL.

[GlowMouse]

Oic, vroeger moest dat anders (en dat was inderdaad erg knullig, want er gebeurt helemaal niks met de data).