Hello,

I have recently created a very simple table with 2 VARCHAR fields. I have then created a quick php script to loop through some inserts (inserted 600000 rows) and then deleted all the rows by doing "delete from Table where field1="abc" which should delete all rows. I then did a select * from Table and the query took 3 seconds! The return result set is empty!

Later I found out that if I do a delete * from Table, without a where clause. And then after tat do a select * from Table, it will take 0.0 seconds. Why is this? How to fix?


1) create table stuff (int id);


2) run php to do insert

for ( $counter = 0; $counter <= 600000; $counter ++) {
mysql_query("INSERT INTO stuff (id) VALUES(1)") or die(mysql_error());
}


3) delete from stuff where id = 1;


4) select * from stuff; <----------this takes 2-3 seconds.

The Problem is not with Where clause Query or anything , with Sql server itself.

do this u willl find the answer by yourself

after u delete the table Check with Sp_spaceUSed 'tablename'

even after deleting u will find pages not deallcoated , the page will exists

do the same with Truncate and see.

Hope u get it know .