On Thu, May 30, 2002 at 03:17:53PM -0500, mos wrote: > 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
How about using a simple binary search? Start by finding out the max auto_increment value and total number of records. That'll tell you how many holes you are looking for. Then chop the space up and query to find the number of rows in each range of values and you'll know where to focus your efforts. That should reduce the number of records you need to read if done well. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 0 days, processed 22,868,489 queries (310/sec. avg) --------------------------------------------------------------------- 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
