I'm creating a database which will house millions of rows of data. The problem i'm having is i have a field which is to store a number of 35 characters. Obviously INT and BIG INT are too small. I am reluctant to use VARCHAR( 35) as I want to search the database via a query number BETWEEN X and Y and am concerned about speed of the query if i use a VARCHAR.

Any suggestions?


Thanks in advance

I reckon decimal might be worth a look.

The numeric values will be whole numbers only

Actually it seems to output ok with test data

Would Numeric(35,0) do the trick then?

right-justify your numbers in a CHAR(35) column with leading zeroes (or spaces if you insist)

not liking the idea of corrupting the data with leading zeros. Also, with them being numbers and i did a query to get a range of numbers, wouldn't they get converted if they were stored as CHARs? Got to remember this is a huge database i'm working on, so speed is essential.

leading zeroes do not actually corrupt numbers

by suggesting that, you are actually admitting that they really are strings, not numbers

so use leading spaces

and converted to what? no, they wouldn't get converted, they'd be CHAR strings

Although i like the idea of DECIMAL, i've just realised that there won't be any calculations as such on these numbers. Also, just found out the number needs a leading 0 (yep, phone numbers). Also PHP won't handle the number of 35 characters very well, ie we'd have to convert it over to a string. Think it might just be easier to store as VARCHAR(35) as before

r937, think you've got it, essentially i should look at them as strings