How to install & Configure MySQL server on CentOS 5 x64 -

1. Login as root user to server.

2. Execute following commands from shell:

# yum install mysql mysql-server mysql-devel
This command will check system configuration and calculates the dependencies required to run MySQL. It will display the list of packages need to be installed and in the end:
……
Total download size: 35 M
Is this ok [y/N]: y
Press ‘y’ like above. This will install mysql server and mysql client on you machine.

3. Now mysql is installed on your server. Now if you execute the following command to connect to mysql (like me)

# mysql
And error message like this below is printed :

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
Then it means mysql is not running. You need to start the the mysql service first. So execute following command:
# /etc/init.d/mysqld start
It will print the following message:
Starting MySQL:                                            [  OK  ]
It means everything is fine. MySQL is up and running and ready to connect on default port 3306.

4. Now you can connect to mysql from shell prompt. Execute following command:

# mysql
You might get this error message:
ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: NO)
Now you might think that you have not provided password that is why above error message. So you try again using -p option like this
# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: NO)
Oops error message again. The MySQL is installed with blank password, so you must be wondering what went wrong. But you need to check with which user name mysql has been installed. (In my case MySQL is installed with default user ‘mysql’).
You need to check ‘/etc/my.cnf’ file and check following ‘user’ property in ‘mysqld’ section:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
So like above ‘mysql’ is default user with blank password. Now you can connect as follow (As password is blank so hit enter when asked to enter password.):
# mysql -u mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>

5. OK. Good going till now. When you try to execute “use mysql;” command and if you get error message like this -

ERROR 1045 (28000): Access denied for user ”@’localhost’ (using password: NO)
Then you must be wondering what the hell happened. I mean even If not root then atleast mysql user should have been able to execute all the SQL statements like - create database, alter database, or even a simple use or select statement, but it is not happening. Somehow it seems that mysql user has no priviledges. Now follow below points.

6. Stop mysql server by executing following command:

# /etc/init.d/mysqld stop.

Once you get the success message go to step 7.

7. Start MySQL in safe with skipping grant tables that stores the password. Execute following command:

# mysqld_safe –skip-grant-tables

8. Now connect to mysql server as root user :

# mysql –user=root

9. Now change the password by executing following commands and then exit:

# mysql > update user set Password=PASSWORD(’new-password’) where user=’root’;
# mysql > flush privileges;
# mysql > exit;
Here new-password should have value which you want to have for root user while connecting to MySQL server.

10. Now Reconnect to mysql-server using ‘new-password’ you set in step 9:

# mysql -u mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>

Now you can do what you want to do. Bingo! That completes the MySQL installation and Configuration.

FOUND_ROWS()

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

In the absence of the SQL_CALC_FOUND_ROWS option in the most recent SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement.

The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to the value later, save it:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM … ;
mysql> SET @rows = FOUND_ROWS();

If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.

SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result.

The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION statements than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole.

The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:

The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.

The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.

If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.

Important
FOUND_ROWS() is not replicated reliably, and should not be used with databases that are to be replicated.

Joomla Reference link for pagination:
http://docs.joomla.org/Using_JPagination_in_your_component