Tuesday, March 22, 2011

Dealing with cyclic data in JAXB

JAXB is a great tool for converting Java data into XML. It does have some shortcomings, one of these is cyclic references. XML is great for tree structures, but any cycles will cause problems as your XML would end up infinitely nesting. There are some tricks explained here, but I was still having trouble marshaling my node data. I ended up needing to use a combination of XmlID + XmlIDREF, XmlTransient, and a wrapping object that transforms the cyclic tree into a list of all nodes in the tree and a reference to the root node.

Saturday, March 19, 2011

Selecting a Random Row With MySQL + PHP

You’d think it was as easy as pie to do, but there is no simple way to obtain a random row that is efficient at the same time.  If you Google for “mysql random row” you might have seen this:

SELECT * FROM `myTable` ORDER BY RAND() LIMIT 1

This looks easy, but if you do an EXPLAIN on that query, you will notice that the entire table is scanned for just one row.
That means the bigger your table is, the longer it will take to execute.
One easy way to get around this, is to split the logic into three parts.

  1. Get a count of all the rows in your table
  2. Find a random number between 0 and the count
  3. Do your query with a LIMIT clause based on that random number
$countQuery = "SELECT count(*) count FROM `myTable`";
$countQueryResult = mysql_query($countQuery);
$count = mysql_result($countQueryResult, 0, "count");
$randRow = rand(0, $count-1);
$randomRowQuery = "SELECT * FROM `myTable` LIMIT $randRow, 1";
By doing this, even though you are doing two selects instead of one, the search time will become constant instead of linear and you will same precious time when your tables become larger.