Category Archives: Databases

The Importance of Indexes

One of my clients has a rather large shop, with in excess of 40,000 products over a couple of thousand multi-tiered categories. Recently the company’s website started to run slowly, seemingly at random times of the day. The reason was surprising.

After some frustrating investigation, the client realised that after each period of high server load he’d often immediately receive an email confirmation of an order being placed on the website.

The order confirmation script, (in this case a HSBC ePDQ callback), queries the database for an order matching a UID passed from HSBC. The query also checks that the order is still marked as un-paid, as added protection against duplicate orders.

SELECT *
FROM `orders`
WHERE `uid` = '$orderIdFromHSBC'
AND `paid` = 0
LIMIT 1

There was no index on `uid` or on `paid` and there were heaps of orders in the table… this customer in particular doesn’t like to archive his old orders.

The addition of a single index on both columns, in order of reference, (it’s more efficient, don’chaknow), fixed the problem immediately.

Share Button

MySQLHell

I’ve just had a nightmare of a development problem. In a nut-shell, I’ve had to import data, (specifically content articles), from one server to another.

This is normally not a problem, but in this instance the source database had each article’s content split into multiple ‘page’ rows. Also, the source database was developed and hosted by another company. They refused to install PHPMyAdmin, (for “security” reasons), and would not give me FTP access. All I had to work with was a terrible database admin system which they’d cobbled together.

Continue reading

Share Button

Random SQL Queries

A year or so ago I was given the task of returning rows of data from a MySQL table in a randomised fashion. This in itself is fairly easy but the project which I was working on displays the data in a paginated format so a constantly shifting random data order would not work. The client also requested that the random order should “re-shuffle” every once in a while.

My solution was to seed the random order with a hash of the current year and month concatenated with each row’s ID value. This means that the random order would be re-shuffled on a monthly basis.

The only caveat of this method is that it’s possible for an end-user of the system to be looking at the site at time of shift, messing up their pagination. But then, with a shifting random order there would always be that possibility!

SELECT *, MD5(CONCAT(DATE_FORMAT(NOW(), '%Y%m'), `id_column`)) AS `rand_col`
FROM `table`
ORDER BY `rand_col` ASC
Share Button