Mysql database query very slow with huge data / rows using CREATE INDEX in PHP

CREATE INDEX visitorhistory_store_created_at_index ON visitorhistory (store, created_at);

The reference use: https://blog.nodeswat.com/making-slow-queries-fast-with-composite-indexes-in-mysql-eb452a8d6e46

Re-index primary key in Mysql database table

set @ROW = 0;

UPDATE `visitorhistory` SET `id` = @ROW := @ROW+1 ORDER BY `id` ASC;

How to export database from terminal / CMD in mysql

mysqldump -u root -p databasename > /var/www/html/lospol_3172019.sql

How to dump Magento 2 Enterprise Edition Database

1. curl -sS https://accounts.magento.cloud/cli/installer | php
2. magento-cloud list
3. login without root
4. magento-cloud db:dump
5. Open login URL in browser like : http://127.0.0.1:5000
6. Update to version 1.23.0? [Y/n] y
7. Continue? [Y/n] y
8. Enter a number to choose a project:
1.  [0] ProjectName (Project) (tx2ia23dv5uk)
2.   Environment ID [staging]: (Press enter key)
9. Enter a number to choose a relationship:
1.   [0] database
2.   [1] database-slave
10. (Press 0 and Enter key) 

How to get Missing / Deleted primary key from Mysql / PHP Database

select a.id + 1 RemoveIds
from jaydip_kansagra a
left join jaydip_kansagra b
  on a.id = b.id - 1
where b.id is null
  and a.id < 14481

14481 = Last Primary Key 

Export Big sql/mysql database using php file

Export Big sql/mysql database using php file

error_reporting(E_ALL ^ E_NOTICE);
ini_set('display_errors', 1);
ini_set('memory_limit', '512M');
$dbinfo = array(
    "host" => 'localhost',
    "user" => 'root',
    "pass" => 'root',
    "dbname" => 'dbname'
);

// Database Config
$sqlhost = $dbinfo["host"];
$dbuser = $dbinfo["user"];
$dbpassword = $dbinfo["pass"];
$dbname = $dbinfo["dbname"];
// filename
$file = date('Ymdhis');
echo shell_exec("mysqldump --add-drop-table -u $dbuser -p$dbpassword`cat /etc/psa/.psa.shadow` $dbname > $file.sql");
//shell_exec("mysql -u $dbuser --password='$dbpassword' --host='$sqlhost' $dbname < $file");
echo 'Finished!<br/>';

Get Address of customer from database Query in Magento PHP Mysql

SELECT
`entity_id` as `entity_id`,
`firstname`.`value` as `First_Name`,
`surname`.`value` as `Surname`,
`telephone`.`value` as `Telephone`,
`country`.`value` as `country`,
`region`.`value` as `region`,
`statecounty`.`value` as `statecounty`,
`city`.`value` as `city`,
`company`.`value` as `company`,
`street`.`value` as `street1`,
`customer_entity`.`created_at`,
`customer_entity`.`updated_at`
FROM
`customer_address_entity_varchar` as `country`
INNER JOIN
`customer_address_entity_varchar` as  `firstname` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `surname` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `telephone` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `region` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `statecounty` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `city` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `company` USING (`entity_id`)
INNER JOIN
`customer_address_entity_text` as  `street` USING (`entity_id`)
INNER JOIN
`customer_entity` USING (`entity_id`)
WHERE
`firstname`.`attribute_id` = 20  &&
`surname`.`attribute_id` = 22  &&
`country`.`attribute_id` = 27 &&
`region`.`attribute_id` = 28 &&
`statecounty`.`attribute_id` = 144 &&
`city`.`attribute_id` = 26 &&
`company`.`attribute_id` = 24 &&
`street`.`attribute_id` = 25 &&
`telephone`.`attribute_id` = 31

How to create custom model and resources file in existing module with database in magento

Step 1 : Update code in config.xml file of the model

<models>
...
...
<kanasagra_jaydip_resource>
...
...
<printsignsize>
   <table>name of your table</table>
</printsignsize>
</kanasagra_jaydip_resource>
</models>

Step 2 : Add new file in Model as Printsignsize.php

<?php
class Kanasagra_Jaydip_Model_Printsignsize extends Mage_Core_Model_Abstract
{

    const ENTITY    = 'kanasagra_jaydip_printsignsize';
    const CACHE_TAG = 'kanasagra_jaydip_printsignsize';
    protected $_eventPrefix = 'kanasagra_jaydip_printsignsize';
    protected $_eventObject = 'printsignsize';
    public function _construct()
    {
        parent::_construct();
        $this->_init('kanasagra_jaydip/printsignsize');
    }
    protected function _beforeSave()
    {
        parent::_beforeSave();
        $now = Mage::getSingleton('core/date')->gmtDate();
        if ($this->isObjectNew()) {
            $this->setCreatedAt($now);
        }
        $this->setUpdatedAt($now);
        return $this;
    }
    public function getPrintsignUrl()
    {
        return Mage::getUrl('kanasagra_jaydip/printsignsize/view', array('id'=>$this->getId()));
    }
    protected function _afterSave()
    {
        return parent::_afterSave();
    }
    public function getDefaultValues()
    {
        $values = array();
        $values['status'] = 1;
        return $values;
    }
 
}

Step 3 : Add new file in model/Resource as Printsignsize.php

<?php
class Kanasagra_Jaydip_Model_Resource_Printsignsize extends Mage_Core_Model_Resource_Db_Abstract
{
    public function _construct()
    {
        $this->_init('kanasagra_jaydip/printsignsize', 'entity_id');
    }
    public function lookupStoreIds($printsignId)
    {
        $adapter = $this->_getReadAdapter();
        $select  = $adapter->select()
            ->from($this->getTable('kanasagra_jaydip/printsignsize_store'), 'store_id')
            ->where('printsignsize_id = ?', (int)$printsignId);
        return $adapter->fetchCol($select);
    }
 
    protected function _afterLoad(Mage_Core_Model_Abstract $object)
    {
        if ($object->getId()) {
            $stores = $this->lookupStoreIds($object->getId());
            $object->setData('store_id', $stores);
        }
        return parent::_afterLoad($object);
    }
    protected function _getLoadSelect($field, $value, $object)
    {
        $select = parent::_getLoadSelect($field, $value, $object);
        if ($object->getStoreId()) {
            $storeIds = array(Mage_Core_Model_App::ADMIN_STORE_ID, (int)$object->getStoreId());
            $select->join(
                array('signprint_printsignsize_store' => $this->getTable('kanasagra_jaydip/printsignsize_store')),
                $this->getMainTable() . '.entity_id = signprint_printsignsize_store.printsignsize_id',
                array()
            )
            ->where('signprint_printsignsize_store.store_id IN (?)', $storeIds)
            ->order('signprint_printsignsize_store.store_id DESC')
            ->limit(1);
        }
        return $select;
    }
    protected function _afterSave(Mage_Core_Model_Abstract $object)
    {
        $oldStores = $this->lookupStoreIds($object->getId());
        $newStores = (array)$object->getStores();
     
        if (empty($newStores)) {
            $newStores = (array)$object->getStoreId();
        }
        $table  = $this->getTable('kanasagra_jaydip/printsignsize_store');
        $insert = array_diff($newStores, $oldStores);
        $delete = array_diff($oldStores, $newStores);
        if ($delete) {
            $where = array(
                'printsignsize_id = ?' => (int) $object->getId(),
                'store_id IN (?)' => $delete
            );
            $this->_getWriteAdapter()->delete($table, $where);
        }
        if ($insert) {
            $data = array();
            foreach ($insert as $storeId) {
                $data[] = array(
                    'printsignsize_id'  => (int) $object->getId(),
                    'store_id' => (int) $storeId
                );
            }
            $this->_getWriteAdapter()->insertMultiple($table, $data);
        }
        return parent::_afterSave($object);
    }
}

Step 4 : Add new file in model/Resource/Printsignsize as Collection.php

<?php
class Kanasagra_Jaydip_Model_Resource_Printsignsize_Collection extends Mage_Core_Model_Resource_Db_Collection_Abstract
{
    protected $_joinedFields = array();
    protected function _construct()
    {
        parent::_construct();
        $this->_init('kanasagra_jaydip/printsignsize');
        $this->_map['fields']['store'] = 'store_table.store_id';
    }
    public function addStoreFilter($store, $withAdmin = true)
    {
        if (!isset($this->_joinedFields['store'])) {
            if ($store instanceof Mage_Core_Model_Store) {
                $store = array($store->getId());
            }
            if (!is_array($store)) {
                $store = array($store);
            }
            if ($withAdmin) {
                $store[] = Mage_Core_Model_App::ADMIN_STORE_ID;
            }
            $this->addFilter('store', array('in' => $store), 'public');
            $this->_joinedFields['store'] = true;
        }
        return $this;
    }
    protected function _renderFiltersBefore()
    {
        if ($this->getFilter('store')) {
            $this->getSelect()->join(
                array('store_table' => $this->getTable('kanasagra_jaydip/printsignsize_store')),
                'main_table.entity_id = store_table.printsignsize_id',
                array()
            )
            ->group('main_table.entity_id');
            /*
             * Allow analytic functions usage because of one field grouping
             */
            $this->_useAnalyticFunction = true;
        }
        return parent::_renderFiltersBefore();
    }
    protected function _toOptionArray($valueField='entity_id', $labelField='materials', $additional=array())
    {
        return parent::_toOptionArray($valueField, $labelField, $additional);
    }
    protected function _toOptionHash($valueField='entity_id', $labelField='materials')
    {
        return parent::_toOptionHash($valueField, $labelField);
    }
    public function getSelectCountSql()
    {
        $countSelect = parent::getSelectCountSql();
        $countSelect->reset(Zend_Db_Select::GROUP);
        return $countSelect;
    }
}

Step 5 : Add following code into your Controller File

$printsignsize = Mage::getModel('kanasagra_jaydip/printsignsize');
                 
$printsignsize
             ->setPrintsignid($printsign->getId())
             ->setSizename($size['sizename'])
             ->setSizeword($size['sizeword'])
             ->setSizeprice($size['sizeprice'])
             ->setStores($answer['votes']);
$printsignsize->save();

Step 6 : Create table in your database.

Step 7 : Don't forget clear the cache. Finish n Enjoy!!!

How to import database using ubuntu terminal or windwos CMD

For linux ubunto

pv /var/www/html/decorati_main.sql.gz | gunzip | mysql -u root -p root

apt-get install pv
pv /var/www/html/decorati_main.sql.gz | gunzip | mysql -u root -p  decorativeplumb

For Windows

mysql -uroot -ppassword mydb > myfile.sql.gz

How to replace word in database in mysql PHP

UPDATE student SET firstname = replace(firstname, 'jaydip', 'Kansagra');




 How to create multilingual static block in controller

$deliveryBlock = Mage::getModel('cms/block')->load('delivery_returns'); echo $deliveryBlock->getTitle(); echo $deliveryBlock->getContent(); 

Search does not return results in magento


Open your file Mage_CatalogSearch_Model_Layer and in method

public function prepareProductCollection($collection)

before return $this;

Add following rows:

Mage::log($collection->getSelectSQL(1), false, 'search.log', true);
Mage::log((array)Mage::getConfig()->getNode()->global->models->catalogsearch, false, 'search.log', true)

 OR

Goto -> admin->attributs->manage attribute-> search sku - open it -> Frontend Properties-> Use in Quick Search (YES )