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;

sql_mode=only_full_group_by is incompatible in PHP mysql query with GROUP BY codeigniter

#1055 - Expression #24 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'customers.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Add $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));") line above your query.

See Example :

$this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
$this->db->select('*, customers.first_name, customers.last_name, customers.id');
$this->db->select("(SELECT count(video_id) FROM customer_videos WHERE c.customer_id = customer_videos.customer_id AND (created_at >= '$from' OR created_at <= '$todate')) as seen");
$this->db->join('customers', 'customers.id = c.customer_id', 'left');
$this->db->group_by('c.customer_id');
$this->db->from('customer_videos as c');
$query = $this->db->get();
echo "<pre/>"; print_r($query->result());exit;
return $query->result_array();

How to log all | full SQL | Mysql queries in Magento 2

Show ALL | Full magento 2 query with Sorting, Pagination and Filter in Magento 2

Open : /var/www/html/magento/jaydip kansagra/app/etc/di.xml

Find 

<preference for="Magento\Framework\DB\LoggerInterface" 

Replace

<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\File"/>
 <type name="Magento\Framework\DB\Logger\File">
      <arguments>
          <argument name="logAllQueries" xsi:type="boolean">true</argument>
          <argument name="debugFile" xsi:type="string">log/sql.log</argument>
      </arguments>
 </type>

Open : /var/www/html/magento/frye-magento/var/log/sql.log

Join Query with LIKE in PHP Mysql

Join Query with %LIKE% function in MYSQL PHP

SELECT `customers`.*, `admin`.`firstname` AS `salespersonfname`, `admin`.`lastname` AS `salespersonlname`
FROM `customers`
LEFT JOIN `admin` ON customers.tags LIKE CONCAT('%', `admin`.firstname, ' ', `admin`.lastname, '%')
ORDER BY `id` DESC

How to get only one record in Join Query in Magento

$orders->getSelect()->join(["Category" => "catalog_category_product"], "Category.category_id=(
    select category_id from catalog_category_product AS p1 where SFOI.product_id = p1.product_id limit 1
    ) AND SFOI.product_id=Category.product_id", []);

SELECT list is not in GROUP BY clause and contains nonaggregated column … incompatible with sql_mode=only_full_group_by

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

will be simply solved by changing the sql mode in MySQL by this command,

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

OR

set @@global.show_compatibility_56=ON;

OR from CMD

mysql -u root -p mg_cstore < db.sql

This is too works me.. I used this becz in my project there are many Queries like this so just change this sql mode only_full_group_by


For permenant solution in ubuntu 16 and 18

Step1) find and modify the config file my.cnf. Usually it’s in /etc/my.cnf or /etc/mysql/my.cnf.

Step 2) Add following code end of the file
[mysqld]sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

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 

How to reset MySQL / phpmyadmin root password on Ubuntu 18.04 Linux

1. The simplest approach to reset MySQL database root password is to execute mysql_secure_installation program and when prompted entering your new root MySQL password:

sudo mysql_secure_installation

New password:
Re-enter new password:

2. Let's stop the currently running MySQL database:

sudo service mysql stop

3. create a /var/run/mysqld directory to be used by MySQL process to store and access socket file:

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld 

4. Start manually MySQL with the following linux commands :

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

5. Confirm that the process is running as expected: 

jobs

6. Access MySQL database without password 

mysql -u root

7. First flush privileges MySQL session

mysql> FLUSH PRIVILEGES;

8. The following commands will reset MySQL root password to "root" 

mysql> USE mysql;
mysql> UPDATE user SET authentication_string=PASSWORD("root") WHERE User='root';
mysql> UPDATE user SET plugin="mysql_native_password" WHERE User='root';

9. Exit MySQL session: 

mysql> exit

10. Terminate current mysqld process 

sudo pkill mysqld

11. Start MYSQL database:

sudo service mysql start

12. If all went well you should now be able to login to your MySQL database with a root password: 

mysql -u root --password=root
mysql> exit

How to get the final sql / mysql query from the grid includes filters and pagination in Magento

Open Following file

Open MagentoRoot/lib/Varien/Db/Adapter/Pdo/Mysql.php

Update false to true

protected $_debug = true
protected $_logAllQueries  = true