SQL_CALC_FOUND_ROWS : Get total records even if limit clause in QUERY

Problem Statement:-

There are many situation when we want to restrict the records that a query returns, but we want the total records that query had matched.Like, If we use LIMIT clause in the query then FOUND_ROWS() will not return the total records that query had found but the records less than or equal to the LIMIT parameter. But we can be interesting in the total records.

For example:-

There is a table customers that have 500 records and if we fire the following query

SELECT * from customers LIMIT 15

then

SELECT FOUND_ROWS()

will return 15. But we want 500 as the result of FOUND_ROWS().

Solution:-

To solve this, there is a clause that returns the total records instead of considering LIMIT clause.

SQL_CALC_FOUND_ROWS is used to get the total records.

For above problem, we can change the query like

SELECT SQL_CALC_FOUND_ROWS * from customers LIMIT 15

now if we fire

SELECT FOUND_ROWS()

then it will return 500.

MySQL : Install MySQL on Linux Machine using yum

In the previous post we have seen how we can install java on the linux machine. This post will guide you how to install MySQL on linux machine using yum command.

Install MySQL

1) To install mysql server type the following yum command:

# yum install mysql-server mysql

2) Turn on mysql service, type the following chkconfig command

# chkconfig mysqld on

3) Start/Stop/Restart using one of the command

# service mysqld start
# service mysqld restart
# service mysqld stop

4) Set root user password

# mysqladmin -u root password NEWPASSWORD

5) Edit /etc/sysconfig/iptables to Add firewall rule

# vi /etc/sysconfig/iptables

6) Add line  

-A INPUT -m state --state NEW,ESTABLISHED -m tcp -p tcp --dport 3306 -j ACCEPT

7) Save and close by pressing Esc and :wq and hit enter

8) Restart iptable service

# service iptables restart

9) Done

Back << Install Java on Linux Machine                 Next >> Install JBoss on Linux Machine

Access MySQL using public IP of VM instance (Google Server)

If you have MySQL on VM hosted on Google server then by default it listens on the local IP address of the machine that is on localhost (127.0.0.1). If you want to access MySQL using public IP of VM then following are some steps :

1) SSH into VM instance.

2) Go to /etc/mysql directory using

cd /etc/mysql

3) Edit my.cnf file. By default, the permissions are u=rw,g=r,o=r so we need to change its permissions to edit using

sudo chmod u=rwx,g=rwx,o=rwx my.cnf

4) Now edit this file using

vi my.cnf 

5) Under [mysqld] directive change bind-address to 0.0.0.0

bind-address=<YOUR-SERVER-IP>

6) Comment out the skip-networking

# skip-networking

7) Save my.cnf by pressing Esc and :wq and hit enter

8) Restart the Mysql service using

 etc/init.d/mysqld restart

9) Login to mysql using

mysql -u root -p mysql

10) Grant access to user using

GRANT ALL ON *.* TO <user_name>@'%' IDENTIFIED BY '<PASSWORD>';

where

 *.* -> database_name.tables (can provide access to particular database)
‘%’ -> IP_address (provide access to particular ip)

11) You need to open TCP port 3306 using iptables or BSD pf firewall using

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

12) Add firewall rule on Google VM instance

Project Name -> Compute Engine -> Network -> Click on default Network

13) Add firewall Rule like

Provide any name and under Allowed protocols and ports -> tcp:3306;

13) Done 🙂

en_USEnglish
en_USEnglish