1

I had the following table that holds phone contacts of users.

CREATE TABLE `contacts_phone` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playerPhone` varchar(45) NOT NULL,
  `friendPhone` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `by_contact` (`playerPhone`,`friendPhone`),
  KEY `by_id` (`playerPhone`),
  KEY `by_phone` (`friendPhone`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

I built the next query that returns mutual friends of my friends:

SELECT SQL_NO_CACHE phones.*, COUNT(phones.friendPhone) AS mutual_friends
FROM (SELECT cp2.friendPhone
    FROM contacts_phone cp1
    JOIN contacts_phone cp2 ON cp1.friendPhone= cp2.playerPhone
    WHERE cp1.playerPhone = 'PHONE_NUMBER') phones
WHERE phones.friendPhone IN (SELECT friendPhone
        FROM contacts_phone
        WHERE playerPhone = 'PHONE_NUMBER')
GROUP BY phones.friendPhone

Until this point everything was ok :)

As our product grew we have collected 13M rows to this table and had a product requirement to add the users id to this table. So I made the next change:

ALTER TABLE `contacts_phone` 
ADD COLUMN `cid` VARCHAR(12) NULL DEFAULT NULL AFTER `id`,
DROP INDEX `by_contact` ,
ADD UNIQUE INDEX `by_contact` (`playerPhone` ASC, `friendPhone` ASC, `cid` ASC),
ADD INDEX `by_cid` (`cid` ASC);

I added the user's id (cid) to the table and filled the missing data. part of the rows didn't have id so I had to use DEFAULT NULL.

I built a different but pretty similar query but now using cid instead.

SELECT SQL_NO_CACHE phones.*, COUNT(phones.friendPhone) AS mutual_friends
FROM (SELECT cp2.friendPhone
    FROM contacts_phone cp1
    JOIN contacts_phone cp2 ON cp1.friendPhone = cp2.playerPhone
    WHERE cp1.cid = 'USER_ID') phones
WHERE phones.friendPhone IN (SELECT friendPhone
        FROM contacts_phone
        WHERE cid = 'USER_ID')
GROUP BY phones.friendPhone

I expected to get a similar query duration but the result was that when querying with cid the duration is doubled.

What am I missing out? Do I have something wrong in my design? Could it be because of the nullable index?

14
  • Create index by (cid, friendPhone) instead of by single (cid).
    – Akina
    Commented Jul 3, 2019 at 9:11
  • @Akina Thank you for the quick reply. can you please elaborate on why your answer will solve my problem and why my way didn't work.
    – Rami
    Commented Jul 3, 2019 at 11:35
  • Look at the subquery in WHERE. Server needs check for cid in index and obtain primary records numbers, then get friendPhone from table by this numbers. When composite index exists server do not need to read a table, it can take friendPhone from the index immediately. Disk operations are slow... decrease them ~ twice decreases elapsed time ~ twice.
    – Akina
    Commented Jul 3, 2019 at 11:45
  • What version of mysql?
    – Rick James
    Commented Jul 3, 2019 at 15:26
  • Is it playerphone or is it playerId? Or cid? What is the minimal set of columns (other than id that is UNIUQUE)? I see multiple issues with the indexes, but I want to understand the columns before presenting an Answer.
    – Rick James
    Commented Jul 3, 2019 at 15:30

1 Answer 1

1

Assuming

  • A "user" is uniquely associated with a "phone"
  • A "user" having a "friend" (also a "user") is indicated in a table that includes UNIQUE(personPhone, friendPhone). (UNIQUE is used to prevent redundant entries.) (in table contacts_phone)

Then the number of friends that 'Rami' and 'Rick' have in common is found

SELECT COUNT(*)
    FROM contacts_phone AS me
    JOIN contacts_phone AS you  USING (friendPhone)
    WHERE  me.personPhone = 'Rick'
      AND you.personPhone = 'Rami'

And the UNIQUE index above serves this query well.

Another approach:

SELECT friendPhone FROM (
    ( SELECT friendPhone FROM contacts_phone WHERE personPhone = 'Rick' )
    UNION ALL
    ( SELECT friendPhone FROM contacts_phone WHERE personPhone = 'Rami' )
                        ) AS x
GROUP BY friendPhone
HAVING COUNT(*) = 2

This finds all our common friends. Wrap a SELECT COUNT(*) ( ... ) AS y to get the count. (This is likely to be slower.)

How many different friends Rami's friends have. First, let's decide on some details using this example:

Rami's friends:  A, B, C, X

A's friends:     D, Rami, C
B's friends:     E, F, A   (guess B doesn't like Rami back)
C's friends:     A, D, Rami
X's friends:     Y, Z

Rami's friends' friends:  D(twice), Rami(twice), C, E, F, A(twice)

Should the count be:

9 -- but that includes dups
6 -- but that goes back to Rami
5 -- excludes Rami, but includes 1st level A and C
3 (D, E, F) -- Maybe this is the count you want??

The desired output is:

A  2
B  0
C  1
X  0

Friend of friend

Let's break the problem into 2 steps. First, see if this gives you the list of friend-of-friends, together with their counts:

SELECT  c2.friendPhone AS friend_of_friend,
        COUNT(*) AS cnt
    FROM  contacts_phone AS c1
    JOIN  contacts_phone AS c2  ON c2.personPhone = c1.friendPhone
    WHERE  c1.personPhone = 'Rami'
    GROUP BY  c2.friendPhone;

That should include people that are not directly your friends. So, let's filter those out. One way is thus:

SELECT  fof.friend_of_friend, fof.cnt
    FROM  ( ... the above query ... ) AS fof
    JOIN  contacts_phone AS c1  ON x.personPhone = c1.friendPhone
    WHERE  c1.personPhone = 'Rami';

(Other approaches might involve HAVING or EXISTS.)

10
  • This query will return only the sum of mutual friends between Rami and Rick. The query that I built return mutual friends for all of my friends.
    – Rami
    Commented Jul 4, 2019 at 6:54
  • @Rami - OK, that takes an extra layer of SELECT -- Do you want counts? Or the actual Phones?
    – Rick James
    Commented Jul 4, 2019 at 16:22
  • the mutuals friends are actually counts
    – Rami
    Commented Jul 4, 2019 at 16:33
  • @Rami - In the example that I spelled out, what is the desired count?
    – Rick James
    Commented Jul 4, 2019 at 21:48
  • 1
    @Rami - I have 2 blogs that may help in understanding indexes, especially "composite" (multi-column) indexes: mysql.rjweb.org/doc.php/index1 and mysql.rjweb.org/doc.php/index_cookbook_mysql
    – Rick James
    Commented Jul 7, 2019 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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