[drupal-devel] Re: MySQL ENUM vs. TINYINT (possible, > performance boost?)

[Konstantin Käfer]

Hello,

I don't think using the ENUM type makes things a lot faster. The 
backdraws are more important in my opinion. First, if the following 
query would set the field to "false": UPDATE table SET bool = 1; 
assuming that the field definition is "ENUM('0', '1') NOT NULL". That's 
because MySQL indexes the ENUM values begging with 1 for the first value 
(thus '0' in our example).

Additionally, NOT NULL does not have any effects in MySQL versions prior 
to 4.1 which could lead to data inconsistency.

Source: http://dev.mysql.com/doc/refman/5.0/en/enum.html

Regards,
Konstantin Käfer



piotrwww at krukowiecki.net schrieb:

>On Sun, Oct 16, 2005 at 08:42:58PM -0400, David Norman wrote:
>  
>
>>I would bet enum is faster (and no I don't have proof). The analyze
>>function in phpMyAdmin even recommends enums in places it recognizes
>>boolean values. Just because mysql would use enum, wouldn't mean pgsql
>>would have to use bool - it could stay as an integer.
>>    
>>
>
>Right, postgres could stay as it is, as long as you don't expect in your
>code special ENUM behaviour.
>
>
>  
>