John Main

Generally speaking dates and times are managed in MySQL (and other SQL engines) in one of two ways; either a ‘YY-MM-DD HH:MM:SS’ string or a Unix timestamp which contains seconds since the Unix epoch – midnight on 1/1/1970.

The first option is more readable, yet there is more overhead in date comparison logic – both the coding and the internal translation and calculation involved. The second is faster to process but, unless you’re some sort of savant, you can’t read it yourself.

So the alternative I use is a BIGINT field containing a single number generated as YYYYMMDDHHMMSS. It is readable, it is lightweight in terms of comparison logic and serves me very well in a number of applications. It’s also just as easy to generate and work with in PHP:

$date1 = date('YmdHis');
$date2 = date('YmdHis', mktime($an_hour, $a_minute, $a_second, $a_month, $a_day, $a_year));
if($date1 > $date2)
    echo 'Whoop!';

I do a lot of data warehousing work these days which often means scripting a series of heavy queries to process data into a more usable format. I built a fairly simple execution framework to make it easier to cron the scripts to run at night when the load on the servers will be lightest but I found that often I would arrive at work the next morning to find that the scripts had failed to complete. After a little testing and logging it appeared that I was getting ‘MySQL has Gone Away’ errors from the Zend adapter I was using for my connection. Bugger.

Actually it turns out that fixing Zend to reconnect automatically when it loses its MySQL connection is a pretty quick hack with some careful use of the ping function.

Zend has a _connect() function in Zend/Db/Adapter/Mysqli.php that begins with a check to see if it has a connection object already created:

if ($this->_connection) {
    return;
}

Unfortunately if ‘MySQL has Gone Away’ this check is too basic to realise. All you need to do to improve this situation is to add a ping to this check:

if ($this->_connection && $this->_connection->ping()) {
    return;
}

And you’re good to go! Zend will automatically reconnect whenever it loses its connection. Winner.

Yeah I know, there are loads of suggestions for this sort of thing on the net but I spent ages searching around for the code and many of the solutions were seriously obfuscated. So here is a direct replacement for an ‘order by rand() statement:

SELECT * FROM my_table ORDER BY RAND() LIMIT 10

Can be directly replaced with:

SELECT my_table.*, FLOOR(1 + RAND() * max_pseudo_table.max_id) AS random_index
FROM my_table, (SELECT MAX(id) - 1 as max_id FROM my_table) max_pseudo_table
ORDER BY random_index LIMIT 30

And with the right indices you will save a world of processor effort 🙂