mysql добавляет дублированный идентификатор поля к текущему полю

Question

У меня есть стол, как:

id     IP     Subnet    Duplicates   Valid
1      foo    16                      1
2      bar    24                      1
3      foo    28                      1
4      foo    32                      1

Я хочу обновить описание с идентификатором дублирующейся строки. что-то вроде:

id     IP    Subnet    Duplicates      Valid
1      foo   16         3,4            0
2      bar   24                        1
3      foo   28         1,4            0
4      foo   32         1,3            0

Вот мой запрос:

update tblSample inner join (
          select
            t1.Id,
            group_concat(t2.Id) dups
          from
            tblSample t1 inner join tblSample t2
            on t1.Id<>t2.Id ) AND
             ((t1.IP >> (32-LEAST(t1.Subnet,t2.Subnet))
                << (32-LEAST(t1.Subnet,t2.Subnet))
             = 
             ((t2.IP >> (32-LEAST(t1.Subnet,t2.Subnet)) 
                << 32-LEAST(t1.Subnet,t2.Subnet)))
          group by
            t1.Id
          ) s on tblSample.Id = s.Id
        set
          Valid=0 ,Duplicates=dups

мой код работает, но он очень медленный (около 53 секунд на 10000 записей)

Как я могу улучшить скорость? Есть ли способ уменьшить операцию сравнения.

Answer 1

Вот решение без самостоятельного объединения в вашем подзапросе, возможно, оно не сильно улучшит производительность, но попробуйте его, а также постарайтесь объяснить это и ваше.

update tblSample t1
join (
    select name, group_concat(id order by id) as description
    from tblSample
    group by name
) t2
on t1.name = t2.name and cast(t1.id as char) <> t2.description
set t1.description = replace(
                        replace(
                           replace(
                             t2.description,
                             concat(',', t1.id, ','),
                             ',')
                           , concat(',', t1.id)
                           , '')
                        , concat(t1.id, ',')
                        , '')
;

Демо Здесь

Answer 2

Вы также можете использовать этот запрос для теста:

UPDATE dupli d
SET description = (
  SELECT CONCAT('duplicate in ',GROUP_CONCAT(`id` ORDER BY id)) 
  FROM (SELECT * FROM dupli) AS d1  
  WHERE `name` = d.`name` AND id <> d.id ) ;

образец

MariaDB [yourSchema]> UPDATE dupli d
    -> SET description = (
    ->   SELECT CONCAT('duplicate in ',GROUP_CONCAT(`id` ORDER BY id))
    ->   FROM (SELECT * FROM dupli) AS d1
    ->   WHERE `name` = d.`name` AND id <> d.id ) ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 4  Changed: 0  Warnings: 1

MariaDB [yourSchema]> select * from dupli;
+----+------+------------------+
| id | name | description      |
+----+------+------------------+
|  1 | foo  | duplicate in 3,4 |
|  2 | bar  | NULL             |
|  3 | foo  | duplicate in 1,4 |
|  4 | foo  | duplicate in 1,3 |
+----+------+------------------+
4 rows in set (0.00 sec)

MariaDB [yourSchema]>