0

I understand that a lot of similar questions have already been asked but I could not find a definitive answer for my question.

Briefly,

  • I have to authenticate users using a REST API, with their phone numbers.

  • Phone numbers are fixed length (10). can be stored in any way suggested.

  • In a table with thousands of phone numbers and the associated user_id, I wish to be able to rapidly query the database and obtain the user_id associated with a given phone number.

  • I read that indexing on strings or random integers is bad.

How should I structure the db to query on phone number most effectively?

0
2

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 user_id and 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 (phone_number, 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 phone_number!

Furthermore, PostgreSQL doesn't have clustered indexes, so there's a lookup involved - with MySQL, you won't have that overhead!

2
  • thanks a lot! I read somewhere that indexing on randomly sequenced integers is bad and wondered if it is the best practice or would cause problems in a database with large number of records. thanks for the clarification. kudos!
    – gkm
    Aug 14 '21 at 8:00
  • @gkm Note phone numbers are not truly random, and will probably cluster well. Generally the biggest difference in phone numbers is the last block of digits, but there's still a high commonality in the first few blocks of digits. GUIDs can be random, and generally don't perform as well when indexed on, but still can even work ok enough in an index, especially if that's what the application calls for. In any case, regardless of the data being stored, if that's what's being predicated on, it's almost always better to index it than to not.
    – J.D.
    Aug 14 '21 at 14:25
0

(MySQL-specific)

"I read that indexing on strings or random integers is bad." -- Old wive's tale.

If that is what you need to search on, use it. Period. Full stop.

And, if all you need is the userid, then have a "composite" index:

INDEX(phone, userid)

That way the lookup can be done entirely in the index's BTree -- probably under 10ms per lookup.

"fixed length is better" -- another old wive's tale.

I agree that phone number are not "numbers". They should be stored as strings (say, VARCHAR(20)). Probably you should remove all punctuation before storing the numbers; that way you don't have to look them up with and without the dashes, spaces, parens, whatever.

As for

PRIMARY KEY(user_id, phone)  -- versus
PRIMARY KEY(phone, user_id)

PRIMARY implies uniqueness; is the pair unique? Will you be looking up rows in this table by other queries? Start the PK with the more useful column. If necessary, add a secondary index with the pair in the opposite order. (And don't bother to make it UNIQUE also.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.