3 seconds to run a select that returns empty set??

[yalag]

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?

[Maq]

I don't understand the problem or what there is to fix.

 

You deleted everything from the table and the query takes 0.0s to return an empty set.  Isn't that what you expected?

[yalag]

sorry if I am not being clear, here are the steps to reproduce the problem:

 

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.

[fenway]

I doubt that this can be reproduced reliably... innodb or myisam/

[yalag]

it can be....I promise. You try it too

[fenway]

You didn't answer my question.

[yalag]

Sorry, it is MyISAM.

OS - Linux Test 2.6.24-23-xen #1 SMP Mon Jan 26 03:09:12 UTC 2009 x86_64 x86_64 x86_64 GNU/Linux

mysql - mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1

 

[Mchl]

Try to OPTIMIZE TABLE

[yalag]

Try to OPTIMIZE TABLE

 

That works, so what's the deal? Am I suppose to call OPTIMIZE TABLE each time I do a delete?

[Mchl]

Not necessarily. Only after massive amounts of data are deleted at once, or as a part of regular maintenance procedure.

[yalag]

Not necessarily. Only after massive amounts of data are deleted at once, or as a part of regular maintenance procedure.

 

So how should I incoporate this into my application? In other databases I didn't have to do this, so when and how often do you recommand my application to do this?

[Mchl]

Hard to say without knowing what your application is supposed to do.

 

Note what manualsays

In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it is not likely that you need to do this more than once a week or month and only on certain tables.

[mandred]

sorry if I am not being clear, here are the steps to reproduce the problem:

 

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.

 

So what is the issue? Just use 4 queries if you have to. CREATE, INSERT, DELETE, then SELECT.

[yalag]

sorry if I am not being clear, here are the steps to reproduce the problem:

 

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.

 

So what is the issue? Just use 4 queries if you have to. CREATE, INSERT, DELETE, then SELECT.

 

The issue is that it is too slow

[mandred]

Then you haven't optimized your tables properly.

 

Show me the table structure, and an example of a row that is inserted.

[Maq]

Show me the table structure, and an example of a row that is inserted.

 

He already did.

[yalag]

Then you haven't optimized your tables properly.

 

Show me the table structure, and an example of a row that is inserted.

 

field1 VARCHAR(50), field2 VARCHAR(100)

 

insert into MyTable values("John", "hello");

 

insert x 60000;

 

delete from MyTable where field1 = "John";

 

Select * from MyTable; <-------this take very long

[mandred]

You're selecting 60,000 rows all at once? Are you outputting the results all on 1 page?

 

Can you give us more details on the application of this? Why do you need to select 60,000 rows, and why does it have to take less than 3 seconds?

[Mchl]

mandred.... the table is empty at that point...

[mandred]

mandred.... the table is empty at that point...

 

Sorry, this is making little sense to me. I don't see the point in doing this.

 

Anywho, why are you using DELETE if you want to empty the table? Use TRUNCATE instead. That will make things much faster.

[Mchl]

I guess the point here was to do a test of sorts. After all it is a bit strange, that querying empty table takes 3 seconds.

[fenway]

If you're really emptying the table all the time, then use TRUNCATE.

 

Also, use multi-valued insert statements for bulk operations.  Even better, use joins to generate the dummy data.

 

MyISAM will leave "holes" during delete operations -- so because your subsequent select statement requires a full table scan, it takes time to page through the records, even though they're "deleted" -- they still exist on disk.

[Zane]

for ( $counter = 0; $counter

mysql_query("INSERT INTO stuff (id) VALUES(1)") or die(mysql_error());

}

 

If you are initiating a query 60000 times then that may have something to do with it being slow.

 

do it like this

for ( $counter = 0; $counter $sql . = "INSERT INTO stuff (id) VALUES(1);\n"; 
}
$query = mysql_query($sql) or die(mysql_error());

[fenway]

Multple statements aren't supported by mysql_query(), AFAIK... see my comment above.

[Zane]

I swear I've done multiple statements in one mysql_query

 

so long as one isn't a SELECT or CREATE statement.