mysql indexing question

Question

I have a "best practices" question, concerning indexing.

I have to index phone numbers, which I normally format the column for an integer. I could separate the number into multiple columns: areacode, suffix, prefix, country code. But since I have to account for international numbers, and the numbers get kinda funny in certain countries, I prefer to keep one column.

So my question is, should I keep the column data saved for integers, characters, or varchars? I do strip out anything non-int related, so varchar is probably not needed.

I have to provide the searching ability for my clients, thus I need to index the number. If all the phone numbers were from the US, then I'd separate the columns, but I'm catering to the international too.

So I'm curious about the indexing part, and other people's practices in this arena. Is it best to index with integers (for something like this), or does it even matter.

As a side note, the phone numbers are not going to be all the same length. Which is why I ask about formatting the column structure in char or varchar.

thanks guys!!

Answer 1

Phone numbers can include # and *, so I would recommend against using integers.
Also the international prefix is + this is to support international prefixes regardless of country you are in.

e.g. in South Africa you need to prefix the country code with 09; in Europe the prefix is 00.
To make the numbers work everywhere you replace the international prefix with + and your cellphone will replace this with the local prefix for dialing abroad.

I'd use a varchar for phone numbers.

Furthermore, I'd use an integer auto_increment as primary key and use the phone number as secondary key, in order to keep performance on InnoDB snappy.
Also remember that people can 'share' a phone number, so it's not guaranteed be by unique.

Answer 2

How large is the table expected to be? The reason I ask is that indexes on ints are going to be smaller, obviously, but on a small table this isn't a major consideration. Using varchar gives you more flexibility to do things like "...WHERE phonenumber like '415%' etc., at the cost of a larger index. If the table is quite large, and the box it runs on is at all memory-constrained, you can run into the situation where your index doesn't fit into memory, sending queries against that index into swap hell. This can be exacerbated by your choice of storage engine: InnoDB prefixes every index with the primary key, for example, which can bloat your indexes if your PK is on a wide field or fields.