>>>> 2014/11/02 13:19 +0100, Zbigniew >>>> So you guys (Jan and "hsv") suggest, that switching from DATE to "more numeric" data type may not be necessary, but using ENUM instead of VARCHAR can be real performance gain, right?
But are you able to estimate, what "boost" can i notice? 5% - or 50%, or maybe even 500%? <<<<<<<< _Maybe_! but I will not venture to measure it. I doubt that it would be big. I just looked ENUM up: it allows 65535 distinct values, which sounds like 16 bits, usually two bytes, and numeric operators would be used. >>>> 2014/11/02 11:19 -0800, Jan Steinman >>>> I would hope that the query optimizer converts '2014-11-02' to the three-bytes internal representation of DATE before doing the query, in which case, DATE should actually be a tiny bit faster than TIMESTAMP. <<<<<<<< That is doubtful. In the processors that I know, one built-in numeric operation is enough for either 2 bytes of ENUM or 4 bytes of TIMESTAMP, but three are needed for DATE. In any case, the C-overhead, since MySQL is implemented in C, overwhelms the cost of any single comparison. The equality comparison is at least simpler than an ordered comparison. >>>> 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, and maybe is useful if the set of dates is well determined. If you use ENUM at least the overhead of translation is built in into MySQL, and, one hopes, costs less than doing it for oneself. 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. If you add the new string to the end, there is, maybe, no great cost to the adding, but if in the middle.... If every new date-string is added to the end, it will, maybe, be in random order. The same applys to your translation table. I suggested TIMESTAMP because I suspect that one built-in comparison (after the optimizer is done with it) is enough, and it allows the appearance of real dates. (If the processor fetches 32 bits at a time (nowadays 64 or more is likly) then a 32-bit type is fetched as swiftly as any other. Both shorter and longer types take longer.) The more I debate this, the better I like TIMESTAMP for your problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
