Missing data in databases can cause bugs in applications or incorrect calculations. Recently, while working on a RETS application, I needed to ensure that not many missing values were encountered in one of the MySQL tables. Although one could easily write a SQL query to find the percentage of missing values, I many times find it easier to first get a visual representation of the amount of missing data there is in the table, and then drill-down further if required. One library that I found that lets you easily get a visual representation of missing data in your database tables is missingno – a Python library.
Read More
Tag: mysql
Pitfalls of assigning a wrong data type to a database column
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.
Read More
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.
Read More
Free datasets for testing database engines
Below is a small list of free datasets with which you can test your database queries or use it for learning and practicing sql query optimization or other things.
Read 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.
Read 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.
Read More