Why is indexing on strings bad? If your application requires it, then it's not bad! MySQL
PRIMARY KEYs are clustered indexes. Clustered indexes put the data physically in order on disk. Furthermore, with MySQL, all of the data in a clustered index is stored in the index itself making lookups faster if the query only contains the fields in the index!
- Phone numbers: phone numbers are not integers. You do not add, subtract, multiply or divide phone numbers (or take square roots... hopefully!), so you should store them as strings, because that's what they are in this context - an arbitrary group of digits which only have a meaning when taken all together as a unit (i.e. a string)!
I'm assuming that your
phone_number are both unique for each record - so you have a choice depending on your requirements, in this case, what is/are your most common query(ies)?
From the question, you are looking up
user_ids using the
phone_number field, so in this case, I would suggest that you have a
PRIMARY KEY of (
user_id) for the fastest possible lookups.
On a table with 10Bn (10,000,000,000) records with two fields on PostgreSQL, I am able to perform a point query (i.e. 1 record based on a PK) in a sub-millisecond time frame - so you should have no problems with query performance for a simple lookup based on
Furthermore, PostgreSQL doesn't have clustered indexes, so there's a lookup involved - with MySQL, you won't have that overhead!