Re: Store tables on different HDD

[Rick James]

Especially for those listening in... The reason why changing from DOUBLE to BIGINT made a big difference has nothing to do with BIGINT, itself. The difference is in having the datatypes consistent across the JOIN.

> This thread has helped me in excess.

You fluff my ego! :)

> I have in the field "cliente" numbers like 91273948571 what if I use this indexed field like varchar?

In that case, I would use VARCHAR (or CHAR or VARBINARY or BINARY). It is not a "number" that you are going to manipulate, it is a string that someone concocted that happens to have only digits in it, correct?

It is 11 digits. Are they always 11 digits? If so, I would pick one of these:
CHAR(11) NOT NULL CHARACTER SET ascii COLLATE ascii_bin
BINARY(11) NOT NULL
Rationale:
* Not DOUBLE or BIGINT -- as mentioned above
* CHAR/BINARY instead of VAR... -- if it is truly fixed length
* ascii -- no o-umlauts, etc, are needed
* ascii_bin or BINARY -- the fastest for string comparisons

On the other side of the argument:
* DOUBLE and BIGINT each take 8 bytes. CHAR(11) and BINARY(11) each take 11 bytes. So, one could argue for saving a little space; I argue for using the 'right' datatype. DECIMAL(11,0) takes 5 bytes, but I don't want to go there.
* CHAR (etc) may be slightly slower than BIGINT, but not enough to worry about.