List:General Discussion« Previous MessageNext Message »
From:Richard Clarke Date:June 1 2002 12:10am
Subject:Re: Finding holes in autoinc sequences
View as plain text  
Genius. I knew there must be a way of tricking Mysql into applying a having
statement and hence doing the whole query in a non join based statement.
I have found the variable features of mysql most useful for a query of the
type, select the top x rows per group. i.e. a limit per group. This is
perfect if you are doing any statistical calculations which involves
gathering the top 100 (or n) rows per id.

Richard


----- Original Message -----
From: "Kevin Fries" <kfries@stripped>
To: "'Richard Clarke'" <clarker8@stripped>; <mysql@stripped>
Sent: Friday, May 31, 2002 6:07 PM
Subject: RE: Finding holes in autoinc sequences


> Richard,
>
> Say, that's really good!
> I'm still not used to taking advantage of those variables.  Clever.
> Also, I think you can remove all but the rows showing gaps by adding a
group
> by and having clause...
>
> select id,@b:=IF(id=@a+1,id,concat(@a+1,"..",id-1)) range,@a:=id,
> @flag:=IF(@b=id,id,"MISSING")
> from seq
> group by id
> having id <> range;
>
>
> > -----Original Message-----
> > From: Richard Clarke [mailto:clarker8@stripped]
> > Sent: Thursday, May 30, 2002 7:48 PM
> > To: mysql@stripped
> > Subject: Re: Finding holes in autoinc sequences
> >
> >
> > Or another approach could be (using same example tables)
> >
> > mysql> set @a:=0; set @b:=0; set @c:=0;
> > mysql> select
> > id,@b:=IF(id=@a+1,id,concat(@a+1,"..",id-1)),@a:=id,@flag:=IF(
> > @b=id,id,"MISS
> > ING") from seq;
> > +----+-------------------------------------------+--------+---
> > --------------
> > --------------+
> > | id | @b:=IF(id=@a+1,id,concat(@a+1,"..",id-1)) | @a:=id |
> > @flag:=IF(@b=id,id,"MISSING") |
> > +----+-------------------------------------------+--------+---
> > --------------
> > --------------+
> > |  1 | 1                                         |      1 | 1
> > |
> > |  2 | 2                                         |      2 | 2
> > |
> > |  3 | 3                                         |      3 | 3
> > |
> > |  4 | 4                                         |      4 | 4
> > |
> > |  5 | 5                                         |      5 | 5
> > |
> > |  6 | 6                                         |      6 | 6
> > |
> > |  7 | 7                                         |      7 | 7
> > |
> > |  8 | 8                                         |      8 | 8
> > |
> > |  9 | 9                                         |      9 | 9
> > |
> > | 12 | 10..11                                    |     12 | MISSING
> > |
> > | 13 | 13                                        |     13 | 13
> > |
> > | 14 | 14                                        |     14 | 14
> > |
> > | 15 | 15                                        |     15 | 15
> > |
> > | 16 | 16                                        |     16 | 16
> > |
> > | 17 | 17                                        |     17 | 17
> > |
> > | 18 | 18                                        |     18 | 18
> > |
> > | 19 | 19                                        |     19 | 19
> > |
> > | 22 | 20..21                                    |     22 | MISSING
> > |
> > | 24 | 23..23                                    |     24 | MISSING
> > |
> > | 25 | 25                                        |     25 | 25
> > |
> > | 26 | 26                                        |     26 | 26
> > |
> > | 27 | 27                                        |     27 | 27
> > |
> > | 28 | 28                                        |     28 | 28
> > |
> > | 29 | 29                                        |     29 | 29
> > |
> > +----+-------------------------------------------+--------+---
> > --------------
> > --------------+
> > 24 rows in set (0.00 sec)
> >
> > To get the MISSING entries you would need to create a
> > temporary table from
> > that query then select where the @flag column = "MISSING".
> > Maybe this is more efficient than the join previously suggested.
> > Maybe mysql team would let HAVING be applied to the
> > 'variably' created rows
> > you could then add having flag_alias = "MISSING" project out
> > just the rows
> > that are missing (saving the need for temporary tables).
> >
> > you could also plug the 10..11 20..21 etc statements into a
> > perl foreach
> > loop (if you were using perl) to automatically create the
> > inner numbers.
> >
> > Ric
> >
> > p.s. maybe there is a way of tricking it into using a having
> > statement to
> > project out the "MISSING" rows.
> >
> >
> > ----- Original Message -----
> > From: "Kevin Fries" <kfries@stripped>
> > To: "'mos'" <mos99@stripped>; <mysql@stripped>
> > Sent: Thursday, May 30, 2002 10:56 PM
> > Subject: RE: Finding holes in autoinc sequences
> >
> >
> > > 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:mos99@stripped]
> > > > Sent: Thursday, May 30, 2002 1:18 PM
> > > > To: mysql@stripped
> > > > 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 <mysql-thread110583@stripped>
> > > To unsubscribe, e-mail
> > <mysql-unsubscribe-ric=lorcom.com@stripped>
> > > Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> >
>
>

Thread
Finding holes in autoinc sequencesmos30 May
  • Re: Finding holes in autoinc sequencesJeremy Zawodny30 May
  • RE: Finding holes in autoinc sequencesKevin Fries30 May
    • Re: Finding holes in autoinc sequencesRichard Clarke31 May
      • RE: Finding holes in autoinc sequencesKevin Fries31 May
        • Re: Finding holes in autoinc sequencesRichard Clarke1 Jun