List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:September 18 1999 4:24pm
Subject:Re: How To INSERT at first free space, rather than at end
View as plain text  
In the last episode (Sep 18), Martin Ramsch said:
> 
> With indexes on 'box', the following query is the best I can think of
> without using a second table:
> 
>   SELECT b1.box+1 AS box
>   FROM   box AS b1 LEFT JOIN box AS b2
>          ON b2.box = b1.box+1
>   WHERE b2.box IS NULL
>   ORDER BY b1.box
>   LIMIT 1;
> 
> But this still is way to slow ...

I just tested this on one of my tables (20000 rows) and it seems to
work fine on 3.23.2, returning the record immediately.  Here's my
explain plan:

explain select t1.TicketNumber + 1 as Ticket 
 from TicketTable as t1 left join TicketTable as t2 
 on t2.TicketNumber=t1.TicketNumber+1 
 where t2.TicketNumber is null order by t1.TicketNumber limit 1;

+-------+--------+---------------+---------+---------+------+-------+-------------------------------------+
| table | type   | possible_keys | key     | key_len | ref  | rows  | Extra               
               |
+-------+--------+---------------+---------+---------+------+-------+-------------------------------------+
| t1    | index  | NULL          | PRIMARY |       4 | NULL | 21241 | Using index         
               |
| t2    | eq_ref | PRIMARY       | PRIMARY |       4 | func |     1 | where used; Using
index; Not exists |
+-------+--------+---------------+---------+---------+------+-------+-------------------------------------+

Which is just about as good as you can get.  Note I also tried the
exact same query on 3.21.26.  I cancelled the select after 5 minutes,
and the explain plan showed it using no indexes at all :)
 
-- 
	Dan Nelson
	dnelson@stripped
Thread
How To INSERT at first free space, rather than at endtony18 Sep
  • Re: How To INSERT at first free space, rather than at endMartin Ramsch18 Sep
    • Re: How To INSERT at first free space, rather than at endDan Nelson18 Sep
  • Re: How To INSERT at first free space, rather than at endBob Kline18 Sep