MySQL empty

I haven’t been able to locate any reference to how I can use query builder to empty an existing MySQL table. Can anybody point me in the right direction?

You can do this without using Yii, for example, export your database to sql in phpmyadmin, save the code of the table, drop table, and then create it, using the code of creating this table.

But if you want, you can create method in your controller, which will delete all items from your table. If you have AR (ActiveRecord) model of your table, create controller’s action, something like this:




//ExampleController.php

//Imagine that our table is called 'tbl_example' and we have AR model class called Example

...

public function actionEmptyTable(){

$all_items = Example::model()->findAll();

foreach($all_items as $item)

       {

$item->delete();

       }

}



You are giving him extremely BAD advices!

Loading the entire data just to truncate a table?! Hell no… What happens if the table contains 10000000000 rows?

@compugator: Check this out:

http://www.yiiframework.com/doc/api/1.1/CDbCommand#truncateTable-detail

This is how you empty a table.

Or you can always do a query [color=#333333][font=monospace][size=2]DELETE FROM tablename; [/size][/font][/color]but its not as fast as truncating.

If the table contains 10000000000 rows, nobody will use ActiveRecord.

  1. You can use ActiveRecord for CRUD with as many rows as you wish, even with 1000000000000000000 rows …the trick is always to use limit and there will be no problem at all.

  2. Your suggestion with loading all the data and deleting it one by one is simply WRONG and will fail with a lot fewer rows…

Yii documentation recommened not to use ActiveRecord for large volumes of data because it is very slow work, so any sane person would not use it in such volume of data.

You absolutely right, it will work extremely bad in big volume of data, but if table contains not so many rows - it will be very simply and fast solution.

You obviously have no idea how AR works.

It doesn’t matter how many rows the table has… it matters how many you load into the memory.

This is what I am trying to tell you.

Your suggestion won’t be fast at all, compared to truncating and with a few hundred rows it will probably begin to fail, if your php configuration is set to use 12-16MBs.

I know that.

But I try to tell you, that you must think before building your application: and if you know that your application will handle large volumes of data, give up the use of ActiveRecord, use DAO.

What if you will need to load all the models based on other needs? In big volumes of data, using ActiveRecord is not the best solution.

For now I build an application, and one of my tables - tbl_client will contain more than 500 000 rows, and believe me, i can tell you that sometimes business logic of application loads very big volumes of data. That’s what you tell: it matters how many you load into the memory. Where is the guarantee that you will not need to do this in application that handle big volumes of data?

And yet you suggest to load the entire table with AR and delete everything one row at a time?!

Nobody should ever do that.

Look, no need to discuss it… I just wanted to let compugator know how a table is truncated, because you were going in the wrong direction.

I am just curious - How would you empty this table tbl_clients if you had to?

I guess that we are dealing with a small amount of data, if we not - I really give bad advice, you right.

Ok, I’ll try to not discuss anymore :)

Personally, I would have done so:

I would save the sql code, that creates table, then drop the table, tnen i would create table :)

But in real world, I would have been severely beaten, if these data disappeared ;)

By the way, thanks for having pointed out my error - we all learn ;)

Here I can agree with you :)