Trying to discover gaps in sequential primary key

[AprilMay]

Hi,

 

I'm currently using mySQL v5.0.51b, and i have a simple table where the primary key is named "id".  I have somewhere around 20,000 records that are supposed to be sequential (ie. 1,2,3,4, etc) , but I have some primary keys that have been going missing for some reason.  (ie. 1,2,4,5,6,9,10).  I've been trying to find a way to select all of the missing ones.

 

Here's what i've gotten so far:  "select id from log where not exist id=(id+1)"

 

but the error i get is: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id=(id+1) LIMIT 0, 30' at line 1

 

any help would be much appreciated.  Thanks.

[Zwiter]

you are doing a redundant query, impossible to execute.

You may have to do a nasted query :

select id from table as a where id not in (select (id+1) from table as b where b.id = a.id)

 

I dont have tested this si i dont know if it really works. I can't see any reason it should not

Z.

[cooldude832]

why is sequentialness important?

[AprilMay]

you are doing a redundant query, impossible to execute.

You may have to do a nasted query :

select id from table as a where id not in (select (id+1) from table as b where b.id = a.id)

 

I dont have tested this si i dont know if it really works. I can't see any reason it should not

Z.

 

Hi there,

 

I've tried this query, and it seems that the NOT is not working ... and it's only concentrating on the "IN" keyword. 

 

I've modified it to use the NOT EXISTS keywords:

select id from table as a where not exists (select (b.id+1) from table as b where b.id = a.id)

 

but now i have the empty set as the result, i think because the condition in the subquery (b.id = a.id) equals the selection in the first query (select id from a)

 

So now I'm trying this:

select id from table as a where not exists (select (b.id) from table as b where (b.id+1) = a.id)

 

because what i want are all the ids where id+1 exists, and then ignoring it.

 

but it's taking forever, i guess because it has to go through all 50000 entries many, many times

 

edit:  it looks like this is working!

[AprilMay]

why is sequentialness important?

 

Sequentialness is important in my case because of error checking.  Gaps in sequential ordering means that something went wrong.  I guess there could be other applications for it (say, a database keeping track of cheque numbers should have it sequential, though not necessarily in the primary key)

[Zwiter]

yeah, i screw a little the query, doing it without testing, but you get my idead.

The only problem is that for each entry, it redo the nested query. So time is an exponential function of number of records.

Z.

[cooldude832]

A better move would be to migrate the data out of the table into a new table and then migrate back (allowing keys to float back into postiioin)

 

This will reset all your key structure of preexisting items but it will get you back to what u want.

[Zwiter]

and all the foreigner keys?

Anyway, if i understand, the goal is only to find gaps, not to re-index the table

[Barand]

I've been trying to find a way to select all of the missing ones.

 

How can you select what isn't there?

 

That's like addressing a group of people and saying "Raise your hand if you're not here"

[Barand]

this will list the missing ids

<?php
$sql = "SELECT id FROM tablename ORDER BY id";
$res = mysql_query($sql);

$prev = 0;
while ($row = mysql_fetch_row($res))
{
    if ($row[0] != $prev+1)
    {
        for ($i=$prev+1; $i < $row[0]; $i++) echo "$i<br/>";
    }
    $prev = $row[0];
}


?>