[drupal-devel] MySQL ENUM vs. TINYINT (possible performance boost?)
I was dinking around in PHPMyAdmin today and noticed this thing called "Propose table structure" which runs: SELECT * FROM `tablename` PROCEDURE ANALYSE ( ) ...and attempts to discover the most optimal fieldtype for each field, based on the data it's currently storing. You'll notice there are ENUMs just about everywhere. And a lot of its output is largely irrelevant. For example, in the watchdog table it recommends as the hostname field: ENUM('127.0.0.1') NOT NULL Which obviously would not be good. :P 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? Angie Byron wrote:
I was dinking around in PHPMyAdmin today and noticed this thing called "Propose table structure" which runs:
SELECT * FROM `tablename` PROCEDURE ANALYSE ( )
...and attempts to discover the most optimal fieldtype for each field, based on the data it's currently storing.
You'll notice there are ENUMs just about everywhere. And a lot of its output is largely irrelevant. For example, in the watchdog table it recommends as the hostname field:
ENUM('127.0.0.1') NOT NULL
Which obviously would not be good. :P
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?
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... -- Piotrek irc: #debian.pl Mors Drosophilis melanogastribus!
participants (3)
-
Angie Byron -
piotrwwwï¼ krukowiecki.net -
Robert Douglass