MySql - char vs. other za što bolju optimizaciju tabela

[adelante]

Interesuje me koji je najbolji tip polja u tabli, koja će sadržati samo jedan karakter 0 ili 1, dali je char(2) dobro rešenje za toili ima bolje.
Treba mi što optimalnije rešenje za brzu pretragu velike baze podataka.

[Ilija Studen]

1 i 0? Znači treba ti boolean tip podataka. Koristi TINYINT.

Više o tipovima (http://dev.mysql.com/doc/refman/5.0/en/column-types.html) (za pred spavanje :) )

[oliver]

a, mozda nesto pogresno kapiram, ali zasto ne ENUM?

[Ilija Studen]

ENUM je u suštini VARCHAR koji ima filter (tj. ne dozvoljava insertovanje nedefinisane vrednosti). Bolje je koristiti numeričke tipove i tipove fiksne širine gde je to moguće zbog performansi - manje prostora zauzimaju, indeksi su lakši itd.

[dinke]

ENUM je u suštini VARCHAR koji ima filter (tj. ne dozvoljava insertovanje nedefinisane vrednosti). Bolje je koristiti numeričke tipove i tipove fiksne širine gde je to moguće zbog performansi - manje prostora zauzimaju, indeksi su lakši itd.

Manual nije bas jasan oko toga, ali evo sta kaze Paul DuBois (MySQL 3rd Edition)
ENUM and SET are classified as string types because enumeration and set members are specified as strings when you create columns of these types. However, the ENUM and SET types actually have a split personality: The members are stored internally as numbers and you can work with them as such. This means that ENUM and SET types are more efficient than other string types because they often can be handled using numeric operations rather than string operations.

MySQL sequentially numbers ENUM members in the column definition beginning with 1. (The value 0 is reserved for the error member, which is represented in string form by the empty string.) The number of enumeration values determines the storage size of an ENUM column. One byte can represent 256 values and two bytes can represent 65,536 values. (Compare this to the ranges of the one-byte and two-byte integer types TINYINT UNSIGNED and SMALLINT UNSIGNED.) Thus, counting the error member, the maximum number of enumeration members is 65,536 and the storage size depends on whether there are more than 256 members. You can specify a maximum of 65,535 (not 65,536) members in the ENUM definition because MySQL reserves a spot for the error member as an implicit member of every enumeration. When you assign an illegal value to an ENUM column, MySQL assigns the error member. (In strict mode, an error occurs instead.)

Here's an example you can try using the mysql client. It demonstrates that you can retrieve ENUM values in either string or numeric form (which shows the numeric ordering of enumeration members and also that the NULL value has no number in the ordering):

mysql> CREATE TABLE e_table (e ENUM('jane','fred','will','marcia'));
mysql> INSERT INTO e_table
-> VALUES('jane'),('fred'),('will'),('marcia'),(''),( NULL);
mysql> SELECT e, e+0, e+1, e*3 FROM e_table;
+--------+------+------+------+
| e | e+0 | e+1 | e*3 |
+--------+------+------+------+
| jane | 1 | 2 | 3 |
| fred | 2 | 3 | 6 |
| will | 3 | 4 | 9 |
| marcia | 4 | 5 | 12 |
| | 0 | 1 | 0 |
| NULL | NULL | NULL | NULL |
+--------+------+------+------+
Prema tome mislim da je enum('0','1') pravo resenje za tvoj problem.

[nixa]

Ovo je bio lep odgovor za Iliju :)

[ivanhoe]

prvo, kao sto Dinke rece, enum se pamti kao broj, koji se onda mapira u zadati skup vrednosti (slicno je recimo u pascalu), TAKO DA JE VRLO EFIKASAN, plus ima prednost nad tynyint i ostalima, sto ne mozes da upises bilo sta u njega...

postoji doduse u mysql i BOOLEAN tip, ali je on za sada obican tinyint, pa mozes bez problema da upises recimo 15 ili 23 u njega, sto je malo glupo...ali najavljeno je da ce se uskoro implementirati pravi bool, sto bi znacilo da prihvata samo 0/1, true/false, yes/no i sl... prednost ovog tipa je sto je on logicniji izbor od enum (kad neko pogleda bazu bice mu odmah jasno cemu sluzi polje) i sto ce u buducnosti moci da se lako prebaci da bude pravi bulijan, ili ako se menja baza, isto tako...

drugo, rad sa poljima fixnih duzina je brzi samo ako su svi podaci u tabeli fixne duzine, jer je onda seek na neki n-ti record mnogo brzi, moze da se izracuna potreban offset u fajlu unapred kao n x velicina rekorda...cim imas jedno varijabilno polje u tabeli, ta prica pada u vodu...

takodje ako su ostali podaci fixne duzine, mysql ce automatski da promeni svaki VARCHAR kraci od 3 u CHAR, tako da IMHO na kraju krajeva je prilicno svejedno jel ces da stavis varchar(1) ili tinyint ili enum...svi ce ti zauzeti po jedan byte, i svima ce pretraga po indexu da radi binarno i vrlo brzo...

[zextra]

Prema tome mislim da je enum('0','1') pravo resenje za tvoj problem.

ovo unosi dodatnu zabunu - enum vrednost '0' dobija interni index 1, a vrednost '1' interni index 2, dakle moze da unese zabunu ako slucajno izostavis ' ' oko broja pa vrednost postane index :). kao sto si i sam napisao, 0 je rezervisana za nepostojecu vrednost:

If you insert an invalid value into an ENUM (that is, a string not present in the list of allowed values), the empty string is inserted instead as a special error value. This string can be distinguished from a “normal” empty string by the fact that this string has the numerical value 0. More about this later.

dakle, po meni, ako ti treba nesto najpribliznije true/false polju, tinyint je po meni dosta dobro resenje. enum i tinyint zauzimaju svaki po 1 bajt. mada, mene je enum uvek privlacio zbog jedne stvari - odredjeni mysql db frontends dozvoljavaju editovanje vrednosti enum polja tako sto naprave select box pa mozes da izaberes neku od validnih vrednosti.

[Ilija Studen]

Hm, moja greška :( Negde sam pročitao da je ENUM u suštini VARCHAR. Kad i gde ne znam, ali je bilo dosta davno.

[dinke]

prvo, kao sto Dinke rece, enum se pamti kao broj, koji se onda mapira u zadati skup vrednosti (slicno je recimo u pascalu), TAKO DA JE VRLO EFIKASAN, plus ima prednost nad tynyint i ostalima, sto ne mozes da upises bilo sta u njega...
Tako je. Uz sve to Enum ti omogućava da koristiš yes - no, true - false i sl vrednosti umesto 0 i 1 što je ipak intuitivnije (naročito ako postoji više od 2 izbora) plus ne moraš u aplikaciji da brineš oko ispisa, jednostavno selektuješ i štampaš vrednost iz polja.

[Ilija Studen]

Ako u bazi koristiš Yes i No za bool onda u aplikaciji treba da imaš funkciju koja će ih prebacivati u odgovarajuću bool vrednost. Ne možeš da koristiš čak ni broj povezan sa vrednošću jer se oni povezuju od 1 pa naviše -> sirova vrednost će uvek vraćati true u uslovima.

Ne vidim nikakvu poentu u uvođenju nove komplikacije da bi se dobilo na čitljivosti sirovih podataka. ENUM('0', '1') => OK, ENUM('Yes', 'No') => not OK po mom mišljenju.

PS: Dodavanjem nekog apstrakcionog sloja sva ova priča manje više pada u vodu jer se sam sloj brine o tipovima. Propel za skladištenje bool vrednosti koristi INT polje, ActiveRecord mapira TINYINT(1) sa bool tipom itd. Zbog ovoga i volim Propel i slične biblioteke: ako ti nisu potrebne max performanse možeš da prestaneš da se brineš o tome na koji način se sirovi podaci skladište. Bitno je da aplikacija dobije ono što si ti definisao...

[adelante]

Ustvari, u mom slucaju postoje samo dve moguce vrednosti 0-false 1-true
a ovde sam procitao par stvari vezano za enum i tinyint http://groups.google.com/group/mailing.database.myodbc/...555d34c932c34728 (http://groups.google.com/group/mailing.database.myodbc/browse_frm/thread/8ed8c21b276fbe1c/555d34c932c34728?hl=en&lr=&ie=UTF-8&rnum=1&prev=/groups%3Fq%3D%2522enum%2Bor%2Btinyint%2522%26hl%3D en%26lr%3D%26ie%3DUTF-8%26selm%3Dc6h60e%252419dd%25241%2540FreeBSD.csie. NCTU.edu.tw%26rnum%3D1#555d34c932c34728)

[dinke]

Ako u bazi koristiš Yes i No za bool onda u aplikaciji treba da imaš funkciju koja će ih prebacivati u odgovarajuću bool vrednost. Ne možeš da koristiš čak ni broj povezan sa vrednošću jer se oni povezuju od 1 pa naviše -> sirova vrednost će uvek vraćati true u uslovima.

Ne vidim nikakvu poentu u uvođenju nove komplikacije da bi se dobilo na čitljivosti sirovih podataka. ENUM('0', '1') => OK, ENUM('Yes', 'No') => not OK po mom mišljenju.
Mašiš poentu. Postoji brdo primera gde 0 i 1 nije prirodno koristiti.

Evi ti primeri:
gender enum('M','F');
domain_available('yes','no');
default_skin('blue','green','silver')
...
Naravno, moze to i sa tinyint, stavis 0 muskarcima, 1 zenama (ili obrnuto) ali tako ne treba raditi. Ende.

Uz sve to, prilikom outputa u nekom frontend-u dovoljno je odraditi recimo:

$query = "select name,sex from users";
$result = $db->query($query);
$data = $result->fetch();
echo "Name: ".$data['name'].", Sex: ".$data['sex'];

Umesto:

$query = "select name,sex from users";
$result = $db->query($query);
$data = $result->fetch();
echo "Name: ".$data['name'].", Sex: ".($data['sex'] ? "M" : "F");

[Ilija Studen]

Slažem se, ali ti primeri su znatno ređi u odnosu na klasični "flagove" (is_logged_on, is_empty, is_draft, is_active...) U suštini, to je moj problem: kad neko kaže bool ja mislim na flag, ne na meta podataka (kao pol sto si ti naveo).

Nemam ništa protiv ENUM kolona (tj. imao sam ranije, ali zato što nisam znao kako se stvarno vrednosti čuvaju u bazi). Čak nasuprot, njihovom upotrebom se rešava mnogo problema.