Perl + DBI: Measuring the time of database queries

It’s often desired to know how much wall clock time the SQL queries take. As with Perl, there’s more than one way to do it. This is a simple way, which involves overriding DBI’s execute() method, so it measures the time and issues a warn() with basic caller info and the time in milliseconds. The [...]

MySQL: LIKE vs. REGEXP() performance testing

Introduction At some point I needed to choose between using LIKE or REGEXP() for a not so simple string match. Without going into the details, the matching string contains a lot of wildcard segments, and while both would have done the job, I thought maybe REGEXP() would benefit from some extra information about the wildcard [...]

MySQL: Enforcing long unique BLOB / TEXT columns with UNIQUE INDEX

Introduction When creating indexes to TEXT / BLOB columns (and their variants), it’s required to specify how many characters the index should cover. In MySQL’s docs it’s usually suggested to keep them short in for better performance. There’s also a limit on the number of characters, which varies from one database engine to another, going [...]

Perl, DBI and MySQL wrongly reads zeros from database

TL;DR: SELECT queries in Perl for numerical columns suddenly turned to zeros after a software upgrade. This is a really peculiar problem I had after my web hosting provider upgraded some database related software on the server: Numbers that were read with SELECT queries from the database were suddenly all zeros. Spoiler: It’s about running [...]

Why MySQL’s (SQL) DATETIME can and should be avoided

I warmly recommend reading the comments at the bottom of this page, many of which go against my point. While I still stand behind every word I said, in particular for web applications (which I believe is the vast majority of MySQL use), the comments below make some valid points, and single out cases where [...]

BLOB, TEXT, and case sensitivity: MySQL won’t treat them the same

When I first discovered that there is both BLOB and TEXT in databases, I was puzzled. They occupy the same amount of disk space, and the database doesn’t alter the data itself anyhow. Why two? Of course, I followed the stream, and went for TEXT and VARCHAR for everything, since I don’t store binary data [...]