homepage Welcome to WebmasterWorld Guest from 54.211.97.242
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Where statements - Int versus varchar
matrix223

5+ Year Member



 
Msg#: 3448756 posted 6:02 pm on Sep 12, 2007 (gmt 0)

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.

Rob

 

Discovery

5+ Year Member



 
Msg#: 3448756 posted 10:26 am on Sep 15, 2007 (gmt 0)

interesting question, its request from senior member please answer above question.

justageek

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3448756 posted 9:21 pm on Sep 17, 2007 (gmt 0)

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.

JAG

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved