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
