If you ever have the need to find the answer to the following:

• What are the number of tables in a MySQL database?
• What are the number of rows in a MySQL database?
• How much space is the MySQL database using up on the server?
• How many tables there are in a MySQL server?
• How many rows there are in a MySQL server?

One of the quickest ways to find out MySQL reporting stats is using PHPMyAdmin. The software is usually installed on a number of servers by default, so its just a matter of locating where in the program to find this info.

This is an example of what the stats look …

Here’s the quickest way to sort data in MySQL after retrieving the last 10 entries with the LIMIT and ORDER BY DESC commands. If you simply do a ORDER BY DESC LIMIT 10, this will return the last 10 results in reverse order (newest first). Adding an inline SELECT command will sort the results after retrieving the last 10 items. (oldest first)

SQL Command

$sql = "SELECT * FROM (SELECT * FROM [TABLE] WHERE ORDER BY modified_date DESC LIMIT 10) AS tbl ORDER BY tbl.modified_date"

This will retrieve the last 10 rows while sorting by date chronologically. I used this SQL command for …

If your site experiences the infamous “Mysql : Too many connections” error, you will need to tune your MySQL server to handle the load of a heavily used website.

There are some configuration variables that you can adjust to make your site handle more connections.
The wait_timeout default in MySQL is 28840 seconds (8 hours), which can seriously limit the number of open connections you have on your page. I adjusted mine to 60 seconds. This frees up the thread after idle timeout, which allows for more connections to connect. The 8 hour timeframe is fine if you only have a few connections to the server. For popular websites, its best to make this value as low as possible. Set this variable to the maximum time your page typically runs. For instance, if you have a page that needs to retrieve some data processed from another server (60-80 seconds) and you only use one mysql_connect, you may lose the mysql connection for processing the remaining parts of the page if your wait_timeout is too low. If the processing takes much longer, a workaround is to use multiple mysql_connects to prevent an idle connection from disconnecting.

I’ve been working on tuning my Apache/MySQL/PHP configuration lately to see how to improve the performance. Lately, I’ve been noticing some crawlers that have been ignoring my robots.txt file and hitting my server really hard with several page requests every second. So, I decided to run some tests to see where I could speed up my website in the code.

A good practice is to check input strings to make sure users don’t put in mySQL commands in your server. For instance, if a username or password POST variable isn’t filtered, there is a potential for an injection like ‘OR myusername=’. In the past, I’ve been using my own PHP toolkit to “clean” the input variables. But recently, I began searching to see if there are a built-in solution in PHP for this, especially since I’m converting a script written in Python that had the filter MySQLdb.escape_string. Enter mysql_real_escape_string()