0

I have a query that I think is taking way too long (as in I've not yet seen it finish!) but I can't figure out why.

The query (which is generated by an application that would be difficult but not impossible to change) is:

SELECT t1.id id1, t2.id id2, 5 weight
FROM ar1 t1 
INNER JOIN ar1 t2 ON (t2.contact_type = 'Individual' and t1.first_name = t2.first_name) 
WHERE t1.first_name IS NOT NULL
 AND t1.first_name <> ''
 AND t1.contact_type = 'Individual'
 AND t1.id < t2.id GROUP BY id1, id2, weight

And a table with 100k rows.

[EDIT: added:] The puropse of the query is to provide a list of unique pairs of records that could be duplicates. Could be because the table has other fields, too, but this query is only looking at the first name field.

CREATE TABLE `ar1` (                                                                            
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,                       
  `contact_type` varchar(64) DEFAULT NULL,
  `first_name` varchar(64) DEFAULT NULL,       
  PRIMARY KEY (`id`),                                                                           
  KEY `k1` (`first_name`,`contact_type`),                                                       
  KEY `k2` (`contact_type`,`first_name`)                                                        
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci                              

Table status:

Name            | ar1
Engine          | InnoDB
Version         | 10
Row_format      | Dynamic
Rows            | 102463
Avg_row_length  | 46
Data_length     | 4734976
Max_data_length | 0
Index_length    | 5275648
Data_free       | 0
Auto_increment  | <null>
Create_time     | 2020-04-21 09:22:1
Update_time     | <null>
Check_time      | <null>
Collation       | utf8mb4_unicode_ci
Checksum        | <null>
Create_options  |

EXPLAIN says this:

***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | t1
type          | ref
possible_keys | PRIMARY,k1,k2
key           | k2
key_len       | 259
ref           | const
rows          | 51232
Extra         | Using where; Using index; Using temporary; Using filesort
***************************[ 2. row ]***************************
id            | 1
select_type   | SIMPLE
table         | t2
type          | ref
possible_keys | PRIMARY,k1,k2
key           | k1
key_len       | 518
ref           | od_civicrm.t1.first_name,const
rows          | 5
Extra         | Using where; Using index
2 rows in set

Notes:

  • There are only 8647 distinct first names in the table.
  • contact_type is Individual for 99.9% of the rows.
  • There's no need to group on a constant (weight), either. But ommitting that does not magically fix it either.
  • I've tried various index combinations.
  • I'm on MariaDB 10.1
  • The servers I've tried it on are not massive - but to my mind this is not a huge recordset?

The ANALYZE result can be downloaded

9
  • Convert the query to SELECT DISTINCT t1.id id1, t2.id id2, 5 weight FROM ar1 t1 INNER JOIN ar1 t2 ON t1.first_name = t2.first_name WHERE t1.first_name IS NOT NULL AND t1.first_name <> '' AND t1.contact_type = 'Individual' AND t2.contact_type = 'Individual' AND t1.id < t2.id. Add index (`contact_type`, `first_name`, `id`).
    – Akina
    Apr 21, 2020 at 9:58
  • Worth a go, but that took over twice as long! (462s) Apr 21, 2020 at 10:48
  • What about SELECT DISTINCT t1.id id1, t2.id id2, 5 weight FROM ar1 t1 INNER JOIN ar1 t2 ON (t1.first_name, t1.contact_type) = (t2.first_name, t1.contact_type) WHERE t1.first_name IS NOT NULL AND t1.first_name <> '' AND t1.contact_type = 'Individual' AND t1.id < t2.id?
    – Akina
    Apr 21, 2020 at 11:45
  • nope, 451s that one. Apr 21, 2020 at 12:57
  • It smacks of "groupwise max" pattern. And it is being done in a very inefficient way. See that added tag.
    – Rick James
    Apr 26, 2020 at 0:35

2 Answers 2

1

What the heck is the intent? It seems to deliver the ids of all pairs of individuals with the same first_name.

It looks like a "group-wise" max, but not quite.

weight seems to be irrelevant since it is the constant "5".

INDEX(contact_type, first_name, id)

may speed it up, even with 99.9% of contact_types being the desired value. The speedup is due to the index being "covering". The order of the columns is important.

It is likely to return a million rows. That, in itself, will take time. And possibly choke the client.

Just looking for dups?

Perhaps you want

SELECT GROUP_CONCAT(id), first_name
    FROM t
    WHERE first_name != ''
      AND contact_type = 'Individual'
    GROUP BY first_name
    HAVING COUNT(*) > 1;

Alternatively, SELECT MIN(id), MAX(id), first_name ..., except that it does not give you all the ids when there are more than 2.

Optimal:

INDEX(contact_type, first_name, id)
4
  • Thanks for your answer and apols you didn't have the intent to work with (see edited question now). Your suggestion follows the same pattern as my answer (i.e. use HAVING) which at first I was excited about until I realised it would only give the first possible pair of matches for a given first name, not all pairs. Yours returns all the IDs but in a string, which -to my knowledge- is not possible/sensible to use for other joins; the output format is important because other data will be joined on to both Ids. Changing this query will be hard but changing its required outputs is not poss. Apr 27, 2020 at 6:58
  • Interestingly, I did try an index with the ID in it too, as you suggest would be optimal - I thought so too since then all the data would be in the index. However MariaDB still chose to use the index without the ID field! Apr 27, 2020 at 6:59
  • @artfulrobot - If there are, say, 5 that match, you want 20 rows to list all "pairs"? Or 10 because of noting that (1,2) and (2,1) are really the same pair?
    – Rick James
    Apr 27, 2020 at 16:11
  • I believe the original algorithm gives 10 (because of the < constraint on ID) Apr 27, 2020 at 21:11
0

OK, I've reduced it to less than 2s (from ~200s, so a 100× improvement).

SELECT d1.id, c2.id, 5 weight
  FROM
  (
    SELECT MIN(id) id, first_name
    FROM civicrm_contact c1
    WHERE first_name <> '' AND contact_type = 'Individual'
    GROUP BY c1.first_name
    HAVING COUNT(*) > 1
  ) d1
  INNER JOIN civicrm_contact c2 ON c2.first_name = d1.first_name 
     AND c2.id > d1.id AND c2.contact_type = 'Individual'
  • Note first_name <> '' works in place of IS NOT NULL AND ... because comparison with NULL results in NULL and in the WHERE context this resolves to boolean false.
  • A key on first_name, contact_type helped but only very marginally.
1
  • The problem with this is that it doesn't generate every a unique set of every possible combinations of duplicates. Apr 22, 2020 at 6:25

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.