3

My Task is to optimize a query, which should check for data inconsistency: Return all leads ids with more than one entries and multiple is_first flag.

| id | lead_id | is_first| (other data) |
|----|---------|---------|-------- ...
|   1|       20|        1|...
|   2|       20|        0|
|   3|       21|        1|
|   4|       21|        0|
|   5|       21|        1|
|   6|       22|        1|

So this dataset should return 21. (20 ok, because is_first is only true/1 once)

Old Query:

SELECT DISTINCT s1.lead_id as lead_id
FROM history as s1
INNER JOIN history as s2 ON
  s1.is_first = 1 AND
  s2.is_first = 1 AND
    s1.id != s2.id AND
    s1.lead_id = s2.lead_id;

New Query: I already increased performance somewhat with this query

SELECT history.lead_id AS lead_id
FROM history
WHERE is_first = 1 GROUP BY lead_id
HAVING count(lead_id) != 1;

But I feel it's not end of the line. Any recommendations?

  • Try using HAVING count(lead_id) > 1 as count cannot have a value <=0. Can you post the actual schema including indexes and the query plan so we can work out if there is anything else that is possible on the dataset. – Mayberg Jun 26 '17 at 15:59
  • "So this dataset should return 21. (20 ok, because is_first is only true/1 once)"... "20 ok" what does that even mean, can you draw up the desired output? – Evan Carroll Jun 26 '17 at 18:45
2

If you dont have it already, you could create a index on both fields: is_first and lead_id;


If it's a table with few record you won't see much difference, so I always recommend to check the statistics from the query.

  • Well... sometimes its better to find another way. Thanks! – Mruf Jun 27 '17 at 9:38
2

I think what you want is something like this..

CREATE TABLE history(id int, lead_id int, is_first bool);
INSERT INTO history(id,lead_id,is_first)
VALUES
  ( 1, 20, 1 ),
  ( 2, 20, 0 ),
  ( 3, 21, 1 ),
  ( 4, 21, 0 ),
  ( 5, 21, 1 ),
  ( 6, 22, 1 );

SELECT *
FROM history AS h1
WHERE EXISTS (
  SELECT 1
  FROM history AS h2
  WHERE h1.lead_id = h2.lead_id
  GROUP BY lead_id
  HAVING count(is_first OR NULL) > 1
);
+------+---------+----------+
| id   | lead_id | is_first |
+------+---------+----------+
|    3 |      21 |        1 |
|    4 |      21 |        0 |
|    5 |      21 |        1 |
+------+---------+----------+
3 rows in set (0.00 sec)

If you just want the lead_id of 21

SELECT lead_id FROM history
GROUP BY lead_id
HAVING count(is_first OR NULL) > 1;
  • Why the OR NULL? – joanolo Jun 26 '17 at 21:13
  • @joanolo it's not strictly needed because you can just use sum() with MySQL (you can't with PostgreSQL). This is for two reasons: count() counts nulls, sum ignore them. In MySQL sum counts true as one. In PostgreSQL you have to write a CASE statement which is longer. This may help in understanding it: dba.stackexchange.com/a/167167/2639 – Evan Carroll Jun 26 '17 at 23:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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