Tuesday, 7 October 2008

One query, many records, one result

Sometimes you need to serialize the results of one simple db query. Instead of fetching any single row and appending it to a string, I thought it could be nice (and faster) to delegate the string creation to the database server.

I found out there's no explicit way to do it in MySQL, but I created a nice workaround using the group_concat clause:

FROM table
WHERE index IN (...)

Usually, group_concat is used to concatenate the results grouped as specified. Instead, I want to concatenate all the results. Passing null we achieve this result: everything is appended to a single record, and we can fetch a single comma separated string with a single query.

Keep in mind the possibility of issues with the size of the result.