How to calculate the size of a MySQL database

Many times we need to calculate the size of a MySQL database through code for purpose like database backup or to check how fast the database is growing for a certain application. The database size can be easily ascertained using phpmyadmin or other desktop tools, but that requires you to manually launch the tool and check. Below we will see how to do the same through a query.
(more…)

Read More

How to profile and debug MySQL queries for speed

Most MySQL applications need profiling for speed enhancements at one time or other. I’ve developed several web applications in the past wherein MySQL queries were a major bottleneck in the application performance. In the past however there were no easy tools to analyse SQL queries in a running application and we programmers had to depend on some makeshift solutions to debug SQL queries. Now, however there are some nice free tools which can help you profile MySQL application queries easily.
(more…)

Read More

How to automatically backup MySQL database to Dropbox

Ever wanted to backup your MySQL database to Dropbox automatically. Well, there is a nice portable solution with Dropbox Uploader. Dropbox Uploader is a BASH script which can be used to upload, download, delete, list files, move, copy and much more from Dropbox. It’s written in the BASH scripting language and only needs cURL to work. I’ve tested it on my Windows/Cygwin environment at it works like a charm.
(more…)

Read More

Avoiding implicit default column values in MySQL

In a non-strict MySQL server mode, columns with NOT NULL and without any DEFAULT values are given implicit default values based on their data types. This can be useful when no values are explicitly set for them during a SQL INSERT. However this can be recipe for disaster if the MySQL server is later configured for strict mode. The following are the general rules governing implicit default column values:
(more…)

Read More

Storing images into a database – resolving a contentious matter

As with many other databases, MySQL provide a BLOB type that allows you to store binary data – images, wav files, videos etc. A frequent question developers have is regarding to storing images in the database. There is much discussion and argument with no final say on the issue. In one of my recent project the same issue was raised; the client and myself discussing the benefits and drawback of storing the images into a database. The project needed storing around 50,000 images, so it was important to get the question resolved satisfactorily.

After much deliberation we settled on using the file system. The major factor in the decision was that we needed the database and images decoupled as we would be having multiple databases using the same set of images. Also in the future it was possible that we would require some processing done on the images (cropping, resizing), which would be tedious and taxing if the images where stored in the database. So in light of these factors we found using a filesystem a suitable solution.
(more…)

Read More

Export a subset of a database table to reproduce a query

One frequently needs to export some MySQL tables along with certain queries that will work with that table for testing. Usually one only needs a subset of records from the table which will work with the selected queries, rather then the complete table. This can be required when the table contains thousands or millions of records and we do not want to export the complete data set, as this can be time consuming during import, or maybe we only want to provide the other user with some selected records for security reasons.

Say we have a simple WordPress database with a SELECT statement like the following:

SELECT * FROM wp_posts WHERE post_status = 'draft'

If you want to only export records that will match the corresponding query above, then we can use the ‘mysqldump’ command-line utility with the ‘–where’ option.
(more…)

Read More

Cherry picking SQL query fields to increase performance

The first SQL query most people learn is the SELECT statement with a query some thing like the following.

SELECT * FROM users WHERE id > 100

Nothing wrong with the syntax itself; but more often than not, what the user really wants is just a few columns from the table as shown in the PHP code below. Here we are only using three columns from the table. The usual practice of developers is to use the * modifier to get all the columns from the table as it is easier then specifying individual column names. It is quicker to select all columns from a table and worry later which to actually use in the code.
(more…)

Read More

How to check when a MySQL table was last updated

I recently had to update a MySQL schema and import new data into the table. But before I could do that I needed to check that no one had updated the table during the last 7 days and no new data had been stored. As the table itself did not have any update field of itself the only other option was to look into the MySQL ‘information_schema’ database.

The ‘information_schema’ database contains a ‘tables’ table which contain the update information for each database and its tables. So all you have to do is grab that information.
(more…)

Read More

Splitting large MySQL dump files

One of the frustrating things with working with MySQL is of importing large sql dump files. Either you get a ‘max execution time exceeded’ error from PHP or a ‘Max_allowed_packet_size’ from MySQL. In a recent task I needed to import a table of around a million records on a remote host, which quickly became an exercise in frustration due to various limitations on the server. SSH was of no help as changing the configuration files was restricted to the root user.

My last resort was to split the huge ‘INSERT’ statements into smaller size files. Manually doing the same is obviously time consuming and error prone; the only other solution is to write a small script to split the insert statements. This was to be a quick hack so the parsing code was to be of minimum complexity. Splitting a sql dump containing extended insert statements is somewhat complex so you need to have the dump file in a simple format – each insert statement should be on its own line as shown below.
(more…)

Read More