Quick Merge in MySQL

This is just a quick tip. If you have a bunch of data that you need to concatenate, take a look at MySQL's GROUP_CONCAT function.

For example, way back when, I wrote about how to add tagging (and subsequently weighting those tags) to an application. It was a very simple approach with each record having a tags field. It was just a simple text field and I stored all the tags with spaces. However, to pull out a list of all tags being used in the system, I returned the tags field from all records, looped through each record, exploding the tags onto an array.

while($ftypes = mysql_fetch_array($rs))
{
  $types = array_merge($types, explode(" ", trim($ftypes[0]) ));
}

Using the GROUP_CONCAT function though, I no longer need to loop through them. I concatenate the list on the MySQL side, which returns me one record with one field.

select group_concat(tags SEPARATOR ' ') FROM posts

Then, on the PHP side, I no longer have to loop, I can just split the first record.

$ftypes = mysql_fetch_row($rs);
  $types = explode(" ", trim($ftypes[0]) );

This may not result in a huge reduction of code but from a performance perspective, I've been experiencing a couple interesting side effects. To test any performance advantages, I created a table with about two million records with some dummy data. The table only has one field to store my tags. The old method took about 23000ms for the first attempt and then an average of about 13000ms for each subsequent request. With the new GROUP_CONCAT method, the first attempt took on average about 7500ms. Already a speed improvement of about three times. But here's the kicker: due to caching, each subsequent request was under 15ms.

With this kind of result, I feel like I missed something somewhere but I ran these tests a few times and everything seems to be on the up-and-up. Inserting a new record into the database brought the select time back up to 7500ms but each call after that was back down to sub-15ms.

These performance tests don't even take into consideration the reduction in needing to loop through everything on the PHP end. All in all, a nice bonus.