0

I have a table where I have a row id that is my primary key. It is a table with ~250 thousand rows. Usually, the id should be a sequential number. Now I want to find out, if there is a number missing in the sequence of numbers.

Example: The table contains the ids

1, 2, 4, 6, 7, 8, 9, 10

That means, in the sequence of numbers from 1 to 10, the numbers 3 and 5 are missing.

How would a MySQL query look like that only returns the missing ids? Is there a way to increment a counter and compare if that id exists?

Gottlieb Notschnabel
  • 9,408
  • 18
  • 74
  • 116
  • Sure, just compare `COUNT(*)` with `MAX(id)`; that's assuming you don't have master-master replication. – Ja͢ck Jul 16 '14 at 13:18
  • @juergend This table is kind of an OLAP table. So I want to know the missing `id`s to check if all "parent items" are being analyzed. – Gottlieb Notschnabel Jul 16 '14 at 13:45
  • @Jack Doesn't return the missing `id`s, but I like this approach. Nice and easy! – Gottlieb Notschnabel Jul 16 '14 at 13:45
  • So you rely on an auto_increment to provide referential integrity while not actually using foreign keys and you rely on the notion that auto_increment is sequential (hint: it's purpose is not to be sequential). tl;dr - your architecture is, by definition, wrong. – N.B. Jul 16 '14 at 13:50
  • @N.B. You're right: My question does not make sense because the original items are not sequential (because items may have been deleted in that original sequence). Thanks for that hint. I was already wondering why `COUNT(*) <> MAX(id)`. – Gottlieb Notschnabel Jul 16 '14 at 13:52

1 Answers1

0

Using a Linux script populate a table test(id:auto-increment) up to your max value of your_table. Then execute something like

select id from test where id not in (select id from your_table)

The output will be the missing.

Gottlieb Notschnabel
  • 9,408
  • 18
  • 74
  • 116
Antonios
  • 190
  • 8
  • Yeah, this helps. Unfortunately it is not dynamically; I have to update this help table each time my original table changes. – Gottlieb Notschnabel Jul 16 '14 at 13:48
  • I am not sure for this statement: Select id+1 from your_table Where not EXISTS (select distinct id from your_table) – Antonios Jul 16 '14 at 14:16
  • Also this post might help you: http://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql – Antonios Jul 16 '14 at 14:18