K
Your table suffers from bad design. There's a violation of normalization. In particular, keeping FIS in one column is a violation of the first normal form. It's good to break FEO into atomars, the name, the name and the Fatherland. From what you say. "check the presence of duplicates with the same fio field." Do you have a situation where one person matches several records (with no primary key number) or whether you want to identify a person not by the primary key, but by the phyo, which is by itself bad and wrong. However, this is not so important in this case.The second problem is the availability of telephone numbers in the table of persons, or the exact situation where one person can have both one phone and two (and theoretically three, four and even eleven, which is not possible in your structure). Thus, a Phones table should be set up in which three fields - the key, PersonId - the external key to the table of persons identifying the person and PhoneNumber - the telephone number. In this way, you could achieve flexibility in the number of telephone numbers, allowing one person to have any number, from zero to infinity (currently, you also have to record NULL or an empty line in the field phone_2? If there is no such number)Well, it's all a scoring inclination. In the current implementation, the request that you have caused unnecessary (no, even blatant) requests. This, of course, has a significant impact on productivity (also to indicate the normal condition) phone_2! It is not necessary to make a separate request for this, which will reassemble all 120,000 records. And that's 120,000 yards for each of the 120,000 records. Words, the number of iterations goes by billions)In principle, such a request should be limited to the use of GROUP BY and hAVING and being something like that: SELECT fio, phone_2
FROM persons
WHERE
phone_2 != ''
AND ACTIVE = 1
GROUP BY fio, phone_2
HAVING COUNT(*) = 1
But your case is complicated by the fact that the result is a field that is not a condition. GROUP BYthat would require an additional request. For example: SELECT p.number
FROM persons p
JOIN
(
SELECT fio, phone_2
FROM persons
WHERE
phone_2 != ''
AND ACTIVE = 1
GROUP BY fio, phone_2
HAVING COUNT(*) = 1
) p2 ON p2.fio = p.fio
I won't say that it will be done in a moment of Oka (to be sure of something, you should try it on your data), but it must take less than five hours.
