[drupal-devel] Re: MySQL ENUM vs. TINYINT (possible, > performance boost?)
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@drupal.org wrote:
Message: 3 Date: Sun, 16 Oct 2005 12:44:35 +0200 From: piotrwww@krukowiecki.net Subject: Re: [drupal-devel] MySQL ENUM vs. TINYINT (possible performance boost?) To: drupal-devel@drupal.org Message-ID: <20051016104435.GA31899@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...
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. -- Piotrek irc: #debian.pl Mors Drosophilis melanogastribus!
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@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.
participants (3)
-
David Norman -
Konstantin Käfer -
piotrwww＠krukowiecki.net