CODEWITHSUNDEEP
mysqlindexing
coffeemonitor
coffeemonitor

Reputation: 13120

MySQL indexing phone numbers

I'm working tons of phone numbers, and many are international.

I've changed my phone numbers table structure to have 5 columns:

`phonenumbers`.`phoneID`
`phonenumbers`.`countrycode`
`phonenumbers`.`areacode`
`phonenumbers`.`phonenumber`
`phonenumbers`.`ext`

At the moment the phoneID is the only column that's an INT, since it's the primary key.

Should I change the other columns to integers? I've heard indexes work best with numeric values, and I'm only storing numbers in each of the columns (no dashes, parenthesis, spaces, etc)

I'm still learning how MySQL works with indexes, so I'm curious how others work with searching for numbers. In this case, I'm sure I'll be searching for numbers that start with a certain known areacode and part of a known phonenumber, or an entire phonenumber.

The part that gets me with indexing and table columns like phone numbers is that I don't always know how long a phonenumber will be. Since countries have different lengths for areacodes and phonenumbers.

In summary, INT vs VARCHAR indexing with numbers.

Upvotes: 1

Views: 2192

Answers (4)

Gustav
Gustav

Reputation: 2940

Use a BIGINT UNSIGNED simple because this forces you to normalize your data. Force your user to store the phonenumber in root level. That means at country level. You could store the country prefix in a separate column to ease the usage.

Everybody types phone-numbers in different ways and this makes it almost impossible to search the data.

E.g. %020123456% will not match 02 0123456. Are you going to search all combinations or just parse it?

This i know from experience, we had to fix manually about 1,000 phonenumbers which we could not script out when installing an auto-dialer.

Upvotes: 0

WizardDBA
WizardDBA

Reputation: 46

In my opinion you have a lot of attributes, that you don´t need, and for phone numbers i usualy use an auto-increment key for id and the phone number is a varchar. This makes it easier the validation with the use of a programming language. It´s my opinion...

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300559

Use varchar for representing phone numbers NOT integers. Otherwise you will find your design decision will come back to bite you.

Also: "I've heard indexes work best with numeric values" - well, that's not strictly accurate: yes the index will take up less space, and more rows will fit per page etc, but an index on a varchar column works perfectly well.

Worry about index size and performance when (1) you have a huge amount of data and (2) when you have measured a performance problem.

Upvotes: 2

nos
nos

Reputation: 229108

Phone numbers are not integers, so don't store them as one, it'll just cause you trouble. The obvious cases are when you have to handle phone numbers too big to fit in an int, or phone numbers starting with a 0.

Moreover, as you want to do prefix matches (phonenumber like '800%'), mysql will be able to use indexes if you're using varchar columns.

You have to figure out how you're querying this data, if you're frequently doing queries like where countrycode='1' and areacode='123' and phonenumber like '2%' , you'd want a compound index on (countrycode,areacode,phonenumber) , and if you're also often doing queries on only the phonenumber, you'd want an additional index only on the phonenumber column, but this is something you have to work out depeding on the amount of data you have and queries you do - work with EXPLAIN to learn how your indexes are used and where they are needed.

Upvotes: 4

Related Questions