how to faster find last record in a table?

[zgkhoo]

//find the last pid 
function findlastPID($pyrtable)
{  
echo "</br>pyrtable=====".$pyrtable;

$result=mysql_query("SELECT PID from $pyrtable ORDER BY PID");
if(mysql_num_rows($result)==0){
	return 1001;
}
else{


while($row = mysql_fetch_array($result,MYSQL_ASSOC)){
	$pid=$row[PID];


}//end while
return $pid+1;

    }//end else
}//end function 

 

this function need loop WHOLE table..wasting resources and wasting time

any solution  that can directly find the last record in a table(which order by ID) which certainly will faster the transaction?

thanks in advance.

[zgkhoo]

if the table consist of 10 thousand record....this function will be extremely slow.

 

[~n[EO]n~]

Assuming you have auto_increment field ID this will fetch the last record

 

SELECT * FROM `table_name` ORDER BY `ID` DESC LIMIT 1;

[zgkhoo]

thanks..but wat is auto increment meant?

wat if i remove a record in the middle of the table?

the auto increment id for this record(assuming 1201)will be disappear or?

will be use for next record insert?

 

 

 

my table structure like this, need to modify or not?

thanks..again

 

			$sql = "CREATE TABLE pyramid
		(
		PID integer(10),
		Serialnum varchar(10),
		UplineSerial varchar(10),
		UplinePID integer(10),
		Downleft integer(10),
		Downright integer(10),
		LeftSerial varchar(10),
		RightSerial varchar(10),
		RowNum integer(10),
		ColumnPosi integer(10),
		ColumnTotal integer(10),
		DownlineAmount integer(10),
		Initial varchar(10),
		ActivateTime datetime,
		Status varchar(10),
		UserID varchar(10),
		ExpiredDate datetime,
		CreateDate datetime,
		Golden varchar(10),

		Primary Key (PID)


       	    
		)";

[fenway]

Your PK isn't auto_increment?!?!

[teng84]

thanks..but wat is auto increment meant?

wat if i remove a record in the middle of the table?

the auto increment id for this record(assuming 1201)will be disappear or?

will be use for next record insert?

 

 

 

my table structure like this, need to modify or not?

thanks..again

 

			$sql = "CREATE TABLE pyramid
		(
		PID integer(10),
		Serialnum varchar(10),
		UplineSerial varchar(10),
		UplinePID integer(10),
		Downleft integer(10),
		Downright integer(10),
		LeftSerial varchar(10),
		RightSerial varchar(10),
		RowNum integer(10),
		ColumnPosi integer(10),
		ColumnTotal integer(10),
		DownlineAmount integer(10),
		Initial varchar(10),
		ActivateTime datetime,
		Status varchar(10),
		UserID varchar(10),
		ExpiredDate datetime,
		CreateDate datetime,
		Golden varchar(10),

		Primary Key (PID)


       	    
		)";

 

auto increment means continuously incrementing the records eg. if you insert a record it will automatically add 1 to the last record

 

if you remove records anywhere that record will be the only one affected

your last record wont disappears

 

will be use for next record insert?  << what do you mean by this.. and suggestion you should google this kind of question sure there are tons of better explanation 

 

[fenway]

I have no idea what either of you are talking about.  MAX(PID) will get the highest uid... but what does "last" mean/

[zgkhoo]

i meant eg..now got three record in the table PID, 1001,1002,1003,

when i delete 1002...wat will happen when i insert another record.?

the system will use the deleted pid which is 1002 or

will use new pid which is 1004.

 

not auto increment....i adding php coding to control the pid...

which one is better? in performance..thanks..

[fenway]

It will use 1004 as the next pid -- as it should.  And don't even think about manging this through php, it's impossible.

[mbeals]

try using 'limit'

If you have a field that mimics the order you wish (PID maybe?) you can do this simple query:

 

SELECT * FROM table ORDER BY indexfield DESC LIMIT 1

 

obviously replacing table and indexfield with your values.  If you are using 'natural order' which is not a good thing to be in the habit of with relational db's, then you might try something like this:

 

$query ="Select `pid` from 'pyramid' ";
$num = mysql_numrows(mysql_query($query));
$offset = $num-1;
$end = 10000*10000

$query = "SELECT * FROM pyramid LIMIT $offset,$end";
$result = ......

 

 

[fenway]

Nothing should "mimic" the order... make it explicit with another field.