homepage Welcome to WebmasterWorld Guest from 50.16.112.199
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Split index with INT & VARCHAR into 2 fields?
LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4469408 posted 9:01 pm on Jun 25, 2012 (gmt 0)

I have a new table that will have a key of the form 12345-adbde, where the 12345 part will always be INT and the abcde part will be a VARCHAR.

Option 1 is to have MyKey field be a VARCHAR(30), with an index on MyKey. Option 2 is to have MyKey1 field be INT and MyKey2 field be a VARCHAR(20), with an index on both MyKey1 and MyKey2.

So searching with option 1 would be:
SELECT *
FROM MyTable
WHERE MyKey='12345-abcde'

Searching with option 2 would be:
SELECT *
FROM MyTable
WHERE MyKey1=12345' AND MyKey2='abcde'

I'd say 99% of the queries would be like this, although maybe 1% would just want the numeric part (easy enough to write for option 2, slightly more complicated for option 1, but still doable).

Would there be any performance advantage by using option 2? Note: there would only be about 2 dozen different values for the numeric part. For the 1% queries, I think there would be a definite performance boost with option 2.

 

Dijkgraaf

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4469408 posted 10:10 pm on Jul 1, 2012 (gmt 0)

One other advantage top option 2 is that you would be using less space to store the numeric part which means you have a smaller database and it can hold more records in memory.
You may also want an index that is across MyKey1 and Mykey2.

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