Find holes in mysql table where ids are not unique

Question

a MySql table is defined as:

CREATE TABLE tbl_misure_30m (
  m_rcd_id          INT NOT NULL AUTO_INCREMENT ,
  m_fon_id          INT UNSIGNED,
  m_timestamp       TIMESTAMP,
  m_fon_rcd_id      INT UNSIGNED,
  m_fon_Leq         FLOAT(4,2),
  m_fon_LsMax       FLOAT(4,2),
  m_Leq_state       INT,
  m_LsMax_state     INT,
  m_fon_mem_block   INT,

  INDEX fon_key (m_fon_id),
  FOREIGN KEY (m_fon_id) REFERENCES tbl_users(fon_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  PRIMARY KEY ( m_rcd_id )

) ENGINE = InnoDB;

where:

m_rcd_id is the autoincremented index
m_fon_id is the "user" id for which I have several m_fon_rcd_id
m_fon_rcd_id is the record id relative to each m_fon_id (so these are not unique).

Unfortunately the database design cannot be changed.

Now, I need to verify that the column m_fon_rcd_id (for each m_fon_id) does not contains holes (in which case I need to know the range).

What I would like to have is a result-set like: | gap_starts_at | gap_ends_at | m_fon_id |

I found this answer but it takes for granted that the various id are unique :

SELECT (t1.m_fon_rcd_id + 1) as gap_starts_at, 
       (SELECT MIN(t3.m_fon_rcd_id) -1 FROM tbl_misure_30m t3 WHERE t3.m_fon_rcd_id > t1.m_fon_rcd_id) as gap_ends_at
FROM tbl_misure_30m t1
WHERE NOT EXISTS (SELECT t2.m_fon_rcd_id FROM tbl_misure_30m t2 WHERE t2.m_fon_rcd_id = t1.m_fon_rcd_id + 1 )
HAVING gap_ends_at IS NOT NULL

Any suggestions?

Thanks in advance!

Gordon Linoff · Accepted Answer · 2014-07-15T21:52:43.833

I would approach this by getting the next value. Then do some arithmetic on that:

select m_fon_rcd_id + 1 as gapstart, next_m_fon_rcd_id - 1 as gap_ends,
       (next_m_fon_rcd_id - m_fon_rcd_id - 1) as gap_length
from (select m_fon_rcd_id,
             (select m2.m_fon_rcd_id
              from tbl_misure_30m m2
              where m2.m_fon_rcd_id > m.m_fon_rcd_id
              order by m_fon_rcd_id
              limit 1
             ) as next_m_fon_rcd_id
      from tbl_misure_30m m
     ) m
where next_m_fon_rcd_id > m_fon_rcd_id + 1;

EDIT:

If you want to do this gaps within m_fon_id, you can just add it in to various parts of the query:

select m_fon_id, m_fon_rcd_id + 1 as gapstart, next_m_fon_rcd_id - 1 as gap_ends,
       (next_m_fon_rcd_id - m_fon_rcd_id - 1) as gap_length
from (select m_fon_rcd_id,
             (select m2.m_fon_rcd_id
              from tbl_misure_30m m2
              where m2.m_fon_id = m.m_fon_id and
                    m2.m_fon_rcd_id > m.m_fon_rcd_id
              order by m_fon_rcd_id
              limit 1
             ) as next_m_fon_rcd_id
      from tbl_misure_30m m
     ) m
where next_m_fon_rcd_id > m_fon_rcd_id + 1;

Thanks a lot for your quick answer! I try your suggestion but it gave me strange results (it seems to not take in account the m_fon_id). For eg. I have a m_fon_id = 123 which has MIN(m_fon_rcd_id) = 11145 and MAX(m_fon_rcd_id) = 11191 with no holes between them but I dont see this boundaries into the resultset. Is there a way to have a resultset like: |m_fon_id|gapstart|gap_ends| ? — Barzo, Jul 15 '14 at 21:28
@Barze . . . Although you mention `m_fon_id` in a parenthetic comment, it is not at all part of the query. It is not clear what role it is supposed to play. You should edit your question with sample data and desired results. — Gordon Linoff, Jul 15 '14 at 21:51
You're right..I'm sorry! I didn't thought about it in this way. Thanks again for your support!! — Barzo, Jul 15 '14 at 21:59

Find holes in mysql table where ids are not unique

1 Answers1