Magento 1.7 – Is Cleaning Logs from Database a Good Practice?

First thing you need to do is create a connection in your module's config.xml. It should look similar to the default_setup in your /app/etc/local.xml. Here you can specify the host to be localhost and then set a different dbname or you can specify a different host completely. I have also used a socket before which works also.

<resources>
    <new_db>
        <connection>
            <host><![CDATA[localhost]]></host>
            <username><![CDATA[db_username]]></username>
            <password><![CDATA[db_password]]></password>
            <dbname><![CDATA[db_name]]></dbname>
            <model>mysql4</model>
            <type>pdo_mysql</type>
            <active>1</active>
        </connection>
    </new_db>
</resources>

Now after this you will be able to connect to this database an perform queries as follows:

$new_db_resource = Mage::getSingleton('core/resource');
$connection = $new_db_resource->getConnection('new_db');
$results    = $connection->query('SELECT * FROM table');

If you want to do this via a model then you can specify the read, write and setup resources as follows. This will again be done inside the resources node in your config.xml and you should replace test with what your model has been setup as.

<resources>
    <new_db>
        <connection>
            <host><![CDATA[localhost]]></host>
            <username><![CDATA[db_username]]></username>
            <password><![CDATA[db_password]]></password>
            <dbname><![CDATA[db_name]]></dbname>
            <model>mysql4</model>
            <type>pdo_mysql</type>
            <active>1</active>
        </connection>
    </new_db>
    <test_write>
        <connection>
            <use>new_db</use>
        </connection>
    </test_write>
    <test_read>
        <connection>
            <use>new_db</use>
        </connection>
    </test_read>
    <test_setup>
        <connection>
            <use>new_db</use>
        </connection>
    </test_setup>
</resources>
<models>
    <test>
        <class>My_Test_Model</class>
        <resourceModel>test_resource</resourceModel>
    </test>
    <test_resource>
        <class>My_Test_Model_Resource</class>
        <entities>
            <test>
                <table>test</table>
            </test>
        </entities>
    </test_resource>
</models>

The model itself will try to find it's connection information in the function getConnection /app/code/core/Mage/Core/Model/Resource.php. If you log the $name passed in you will see values like poll_write, tag_write and cms_read where the first part matches the models section in the config.xml, in our case you would see test_write, test_read or test_setup. If it cannot find a connection matching this then it will use the default connections core_read, core_write or core_setup

If you have to do a mass update of one (or more attributes) to the same value, you can use:

$this->_objectManager->get('Magento\Catalog\Model\Product\Action')
    ->updateAttributes($productIds, ['status' => $status], $storeId);

In this example, we're updating all products matched by id ($productIds is array of product IDs), by setting their status attribute to the $status value, for store $storeId.

If you want just to update single attribute in the collection of products, you can use (in your case):

...
foreach($productCollection as $product{
    $product->addAttributeUpdate($attributeCode, $value, $store);
}
...

$attributeCode represents attribute name which has to be saved

$value represents the value which you want to set on the entity

$store represents store id, it can be $product->getStoreId()

Problem with addAttributeUpdate() is that updates attribute, but it doesn't update currently loaded product model. Take a look at the implementation in Magento\Catalog\Model\Product:

/**
 * Save current attribute with code $code and assign a new value
 *
 * @param string $code  Attribute code
 * @param mixed  $value New attribute value
 * @param int    $store Store ID
 * @return void
 */
public function addAttributeUpdate($code, $value, $store)
{
    $oldValue = $this->getData($code);
    $oldStore = $this->getStoreId();

    $this->setData($code, $value);
    $this->setStoreId($store);
    $this->getResource()->saveAttribute($this, $code);

    $this->setData($code, $oldValue);
    $this->setStoreId($oldStore);
}

In that case it's better to use something like, productResource is Magento\Catalog\Model\ResourceModel\Product :

...
foreach($productCollection as $product{
    // its sugessted to inject resource as dependency than using deprecated getResource() method. 
    // check reference for more information.
    // $product->getResource()->saveAttribute($product, $attributeCode);
       $this->productResource->saveAttribute($product, $attributeCode);
}
...

Lack of this solution becomes visible when you're working on large collections. That's why we have iterators which grabs data from database, one by one, and on that way, it prevents memory exhaustion.

...
$this->_dataObject = $this->_objectManager->get('Magento\Catalog\Model\Product');

$iterator = $this->_objectManager->get('Magento\Framework\Model\ResourceModel\Iterator')

$iterator->walk($productCollection->getSelect(), [[$this, 'walkCallback']]);
...

public function walkCallback($data)
{
    $this->_dataObject->reset(); // clean up shared object
    $this->_dataObject->setData($data['row']); // map data to product model
    $this->_dataObject->setDescription('test');
    $this->_dataObject->getResource()->saveAttribute($this->_dataObject, 'description'); // save only changed attribute instead of whole object
}

Those are ideas which you can use for manipulating collections.

Reference: https://github.com/magento/magento-coding-standard/pull/189