I have two options to call up data. One involves using a WHERE something = intvalue and the other using a WHERE something = varcharvalue.
Its somewhat obvious to me that calling up the data match using the int value is more efficient, but I was hoping someone knew more details about why it was so I could better understand my options. It will ultimately effect a few other minor things down the road but that can be reworked based on this decision.
Note both columns of data will be indexed, which really begs the question does it even matter? I guess what I'm wondering, is - is there an extreme difference between one versus the other?
Thanks for any input, I know this kinda sounds like a dumb question.
- My grandfather used to say that the only dumb question is the one not asked :-)
I deal with a decent amount of data and the biggest advantage is space for me.
By relating text to an int I save a LOT of memory space so I know I get the maximum usage out of my RAM memory which is where I like to run all my queries from.
If you don't plan to have a lot of data then just using a varchar and letting MySQL keep the query in cache will be about as fast as having an int. But, if you have a lot of text data you can run out of RAM fairly quickly.
For example...let's say you store URL's as a varchar. ASCII characters are 7 bits plus an ending bit set to 0 for 8 bits total per character. The amount characters that the URL to this site would use is 22 just using the domain name. In the same space I can fit almost 3 int type URL's since they are only 4 bytes in length using a standard int type. Add in some longer URL's and the advantage is even better.
Every one will have different requirements and resources where it may be too much overhead to translate text to integers and back but for me I always do int translations since I do a lot of analysis where the only thing needed to see are the numbers at the end. I also never have less than 16 gig of RAM in any machine including development machines so that may give you an idea of the amount of data I play with daily and why I use ints. Your mileage may vary.