A recent debugging session on a web application surfaced a recurrent issue in database design – that of assigning a wrong data type to a database field.
(more…)
mysql
Statistical distribution of column values in MySQL
Many times we need to get a statistical distribution of values in a database table. Say you have a e-commerce shoe store having a product table with the following fields and values. As this is only an example I’ve limited the table to a few items; there will hundreds of rows in a real-life table.
(more…)
Combinatorial row pairing in databases
While working on a database design I came across the problem of finding combinations or ‘cartesian product’ between all the rows between one table to another table, but not repeating the same permutations.
Take the ‘Teams’ table below which lists four cricket teams. We want to create a combination list of matches between each teams.
(more…)
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…)
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…)
Data Quality in web applications
There is nothing painful than having to rebuild a database after you find that the records in the database are corrupted. Data corruption in databases can take many forms – from allowing inaccurate data to be entered to data field truncation due to inadequate field length. This post gives an introduction to the data quality issues you will encounter when designing database applications.
(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…)
MySQL transactions in NodeJS
In a previous post we saw how to access and query MySQL from NodeJS. In this post we will see how we can use MySQL transactions in NodeJS. We will be using the same library as used before, so if you have not installed it go ahead and install it. The library supports simple transaction at the connection level.
(more…)