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

[David Norman]

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.

If it makes you feel better to leave tinyints alone, I read this to say
tinyint should stay for boolean values:
http://dev.mysql.com/doc/refman/4.1/en/other-vendor-column-types.html


drupal-devel-request at drupal.org wrote:
> Message: 3
> Date: Sun, 16 Oct 2005 12:44:35 +0200
> From: piotrwww at krukowiecki.net
> Subject: Re: [drupal-devel] MySQL ENUM vs. TINYINT (possible
> 	performance	boost?)
> To: drupal-devel at drupal.org
> Message-ID: <20051016104435.GA31899 at mallorn.ii.uj.edu.pl>
> Content-Type: text/plain; charset=us-ascii
> 
> On Sat, Oct 15, 2005 at 11:26:15PM +0100, Robert Douglass wrote:
>> Angie Byron wrote:
>>> However, we do have a lot of TINYINT fields that are only ever going to 
>>> store either 0 or 1, which could be changed to ENUM('0','1') NOT NULL as 
>>> the analyzer recommends.
>>>
>>> Any MySQL gurus who could shed some more light on this? Is it worth 
>>> doing? Would we gain any performance benefit from this or would it be 
>>> too negligible to notice?
>> And the other, bigger, question is; where does this leave PostgreSQL 
>> support?
> 
> PostgreSQL of course does not have ENUMs ;)
> It does have bool data type that can accept '1' and '0' as values.
> Unfortunatelly on SELECTs it returns 't' and 'f' for true and false
> falues, so I don't think it can be used as a substitute for ENUM, unless
> you use ENUM('t', 'f') for mysql.
> There would be other differences, like mysql allowing incorrect values
> etc...
> 
> I don't believe (without any proofs) that ENUMs would provide any/significant 
> speed increase...
> 
>