MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:November 4 2014 7:04pm
Subject:Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?
View as plain text  
> From: (Halász Sándor) hsv@stripped
> 
>>>>> 2014/10/29 20:56 +0100, Zbigniew >>>>
> if instead of textual labels I'll use SMALLINT as simple integer "code" for each
> different "label" (and in my program a translation table)? 
> <<<<<<<<
> This, in effect, is ENUM...

Well, not really! With INTEGERs, your referential integrity is external to MySQL, and has
to be managed.

> ... and maybe is useful if the set of dates is well determined...

I was not suggesting it for dates. The OP appeared to have a well-defined set of strings
in a VARCHAR field — THAT is what I suggested ENUMs for!

> There is a design cost in using ENUM: If you find that your first set of dates is too
> small, later, with ALTER TABLE, you have to change the type.

Again, the suggestion for ENUM was to replace a constrained set of VARCHARs, and yet, you
raise a valid point.

What is the update frequency of those VARCHARs? If you're adding them often — or if
you need to occasionally change their value — I'd use another table with a
reference.

If they're immutable and new ones are not added often, there's no "design cost" at all to
using ENUMs. I'd argue there's a higher maintenance cost to NOT using them!

:::: Jan Steinman, EcoReality Co-op ::::

Thread
Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?Zbigniew29 Oct
  • Re: Using INTEGER instead of VARCHAR/DATE - is this a way tofaster access?hsv31 Oct
Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?Jan Steinman31 Oct
Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?Jan Steinman31 Oct
  • Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?Zbigniew2 Nov
    • Re: Using INTEGER instead of VARCHAR/DATE - is this a way tofaster access?(Halász Sándor)3 Nov
Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?Jan Steinman2 Nov
Re: Using INTEGER instead of VARCHAR/DATE - is this a way to fasteraccess?Johan De Meersman4 Nov
Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?Jan Steinman4 Nov
Re: Using INTEGER instead of VARCHAR/DATE - is this a way tofaster access?(Roberta Jaskólski)6 Nov
  • Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?Zbigniew6 Nov