php: quickest way to generate a 5-digit number not

Question

Suppose Table1 contains column orderid (not a key, although it's NOT NULL and unique). It contains 5-digit numbers. What's the best way to generate a php var $unique_var that is not in that column. What could be important, from 10% to 30% of 5-digit numbers are in the table (so generating a number and checking while (mysql_num_rows() ==0) {} is not a best way to find one, are there any better solution for performance?).

Thank you.

Answer 1

I would suggest finding the biggest number (with a MAX() clause) and start from there.

Answer 2

Here are a couple of suggestions. Each has its drawbacks.

1. Pre-populate your table and add a column to indicate that a number is unused. Select an unused number using LIMIT = 1 and mark it used. This uses a lot of space.
2. Keep a separate table containing previously used numbers. If that table is empty, generate numbers sequentially from the last used number (or from 00001 if Table1 is empty). This requires some extra bookkeeping.

Answer 3

If there is just 10-30% of numbers already taken - then it means that only 10-30% of queries will be performed at least twice. Which is not a big performance issue at all.

Otherwise - just create all-5-digit-numbers-list table (just 100k rows) and remove all that exist. When you need another random number - just pick one and delete.