A simple and fast way to find the gaps is to use a self LEFT JOIN, such as:
select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; The result set will show any row in seq where there was no record with ID one greater than that record's value. You'll get back the last row, and any rows which don't have a next-higher neighbor. To get my example above to work, use: create table seq ( id int auto_increment not null primary key); insert into seq values(); ** repeat to get rows 1..30. ** delete from seq where id between 10 and 11; delete from seq where id between 20 and 21; delete from seq where id = 23; select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; +----+------+ | id | id | +----+------+ | 9 | NULL | | 19 | NULL | | 22 | NULL | | 30 | NULL | +----+------+ Note that this doesn't show where the gaps end. You can further enhance the query by looking backward as well...getting fancy output: select a.id, case when c.id is null and b.id is null then '<>' else (case when c.id is null then '<' else (case when b.id is null then '>' else '' end) end) end gaps from seq a left join seq b on a.id + 1 = b.id left join seq c on a.id - 1 = c.id where b.id is null or c.id is null; +----+------+ | id | gaps | +----+------+ | 1 | < | | 9 | > | | 12 | < | | 19 | > | | 22 | <> | | 24 | < | | 30 | > | +----+------+ Note here that there's a gap between 9 and 12, between 19 and 22, and between 22 and 24. There's also a gap before 1, and one after 30, but this just tells us where the range ends. nulled Outer joins are very handy. Kevin Fries > -----Original Message----- > From: mos [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 30, 2002 1:18 PM > To: [EMAIL PROTECTED] > Subject: Finding holes in autoinc sequences > > > I'm going to be tackling this problem in a few days and I > wanted to bounce > it off of a few MySQL heads first to see if it generates any > ideas. (or > sparks?<g>) > > Here's is the problem. I have an auto-inc column and rows > will get deleted > from the table and of course it will create a hole in the > sequence which is > fine. But I want to track which rows have been deleted by finding the > holes. (I will probably keep track of the deleted rows as they get > deleted, but occasionally I will need to verify this by > scanning the table.) > > Example: > > Original squence of Rcd_Id: 1,2,3,4,5,6,7,8,9,10 > > After deleting rows 5 and 9 we get: > Rcd_Id: 1,2,3,4,6,7,8,10 > > Now is there any SQL statement that I can use to quickly and > efficiently > find the 2 missing rows in this example? The only thing I've > come up with > is to write a PHP program to loop through the Rcd_Id's in > order and see > which ones are missing (the query would fetch 10k rows at a > time so it > doesn't consume too much memory). The table could get rather > large (>1m > rows) and I need something that doesn't consume a lot of > memory or time. > Does anyone have any ideas? TIA > > Mike > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
