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
