0

I have a query that gets some data about customers. This customers can have three phone numbers and these can be repeated.

To count the number of times that these phones are repeated a partner have create a subselect:

(select count(*) 
from TABLE_A as k 
where (k.phone=a.phone or k.phone2=a.phone or k.phone3=a.phone) 
and k.id!=a.id) as repetitionsPhone1

This is inside a bigger select like this:

    SELECT a.*,c.*,b.*, 
    (
        select count(*) 
        from TABLE_A as k 
        where (k.phone=a.phone or k.phone2=a.phone or k.phone3=a.phone) 
        and k.id!=a.id
    ) as repetitionsPhone1
    FROM a
    left join c on a.id=c.id
    left join b on a.id=b.id

This query takes for 50 rows about 30 seconds, and it should return about 2000 rows every single day.

To optimize this I use explain and I see that this subquery was the problem so I searched and I tried this:

SELECT phn,sum(count) as phoneRepetitions
from (
    select k.phone1 as phn, count(*) as count
    from k
    group by k.phone1 
        UNION
    select k.phone2 as phn,count(*) as count
    from k
    group by k.phone2
        UNION
    select k.phone3 as phn,count(*) as count
    from k
    group by k.phone3
) as aux
    group by phn

And this returns #1062 MYSQL error: Duplicate entry for key 'distinct key'

First of all I would like to solve this problem. Anyone knows what is happening? This error seems logic in an insert statement, but in select?

And later, this will help to improve the big select that I must optimize? I will have to do this for the three columns.

Thank you.

mrc
  • 2,845
  • 8
  • 39
  • 73
  • union won't make it more efficient for sure – Dimgold Jul 06 '17 at 11:19
  • @Dimgold Thanks for your answer. Do you know a solution then? – mrc Jul 06 '17 at 11:23
  • I'm not sure you can union a table to itself with the same alias? E.G Would you would need distinct aliases for each union? Like select k.phone1 as phn, count(*) as count from ALTAS_DET k1 group by k.phone1 UNION select k.phone2 as phn,count(*) as count from ALTAS_DET k2 group by k.phone2 UNION select k.phone3 as phn,count(*) as count from ALTAS_DET k3 group by k.phone3 – MrrMan Jul 06 '17 at 11:23
  • @MikeDWakelyn I erase the aliases and it does not work, neither with other and different aliases :( – mrc Jul 06 '17 at 11:25
  • In the first instance, see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query). Afterwards, we can talk about optimization. – Strawberry Jul 06 '17 at 11:31

2 Answers2

0
SELECT count(*) from

  (SELECT phones1 FROM k
    union
  SELECT phones2 from k
    union
  SELECT phones3 from k)

AS SumCountPhones

This seemed to work for me.
Solution as per How do I add two count(*) results together on two different tables?

You can keep stacking the unions.

MrrMan
  • 158
  • 1
  • 13
0
SELECT 
COUNT(*) AS CountOf FROM
  table1
    INNER JOIN
table2
GROUP BY phone1 , phone2 , phone3
HAVING COUNT(*) > 1
HariKishore
  • 149
  • 8