如何使用手机联系人设计一个mysql数据库，并找到使用相同应用程序的任何联系人？

Question

Lets say i have an app which fetches all phone contacts. Then checks in the database to find any contact is using the same app. If so then add him as a friend. Lets say a user has 1000 contacts and total users in a database are 1 million. In that case it has to check these 1000 contacts against 1 million users ? Is there any other way i can do a fast lookup ?

Answer 1

You can check each contact against a 1M rows table, and repeat 1000 times. That would be sub optimal.

I would advice to create a temporary table with the 1000 contacts, create and index on the unique_id of each contact, then join the temporary table with your 1M table on which you of couse already have an index on the unique_id. Store the result in another table (for example "contacts_to_add") and delete the temporary one. (MySQL does this automatically when you use the CREATE TEMPORARY TABLE sentence)

This should be fairly quick.