$ next_avail ID - 我的网站需要的一种auto_increment - 查询的速度

I'm working on a portal / large website and I have a question as to how to optimise my mySql / PDO queries in a special case.

I developed it this way : when I'm inserting an iD (unique / primary) I do the following code to find out the highest unused id in a specific table, and then do an INSERT with that id ($next_avail).

After a short chat last days on stackoverflow I got the ideea that AUTO_INCREMENT is best for this action.

But now, I realize that in most of the cases I also use $next_avail (the value of the AUTO_INCREMENT how it would be) to insert in other tables as a column, as well.

So my code makes sense for these inserts.

My question is, how would this code below work for millions of rows as speed, for each insert I do depends on it.

Please write comments and ask me to clarify what is not clear for you, in this question.

Thanks, Adrian

$next_avail = 1 ;
                    $stmt = $db->prepare("SELECT news_id from mya_news ORDER BY news_id DESC LIMIT 1");
                    $stmt->execute();
                    while ( list($id) = $stmt->fetch(PDO::FETCH_BOTH) ) {                                                   
                        $next_avail = $id + 1;
                    }

You don't need to do that, just use an auto_increment in your table, and also use the PHP functions to get the last_inserted_id mysql_insert_id()

take a look to this sites: http://php.net/manual/en/function.mysql-insert-id.php

this one is with PDO http://php.net/manual/en/pdo.lastinsertid.php

Possibly use max rather than ordering the results and using a limit.

However this is very risky. A chance that 2 bits of processing will both get the same $next_avail at the same time. I would suggest changing the order you insert rows (or even inserting a dummy row to get the next id, and updating the row later on) to use the AUTO_INCREMENT column value