Ideas

PHP or SQL?

PHP programmers sometimes ask whether it is better to code a certain database problem in PHP or SQL.  A recent practical example illustrates that SQL frequently is the best solution for speed of execution.

The problem was to scan a MySQL table to find duplicate rows.  The table, which we call the entity_tag table, has three columns: id, contact_id and tag_id.  The id column is the primary key and is guaranteed to be unique.  The other two columns can contain arbitrary integer values, and the schema does not prevent insertion of a row containing a (contact_id, tag_id) pair that duplicates a row already present in the table.

The first attempted solution was to read the table into memory and use PHP code to find duplicates.  On a table of about 17000 rows, this took 10 minutes of execution time using PHP 5.2 and MySQL 5.0.

This was replaced with a solution that did the comparison in SQL and returned only the primary keys of the duplicates:

SELECT t1.id, t2.id  FROM entity_tag AS t1, entity_tag AS t2 WHERE t1.contact_id = t2.contact_id AND t1.tag_id = t2.tag_id AND t1.id != t2.id;

On the same data and software, execution time was less than one second.

Using the PHP PDO extension's PDOStatement::fetch() method with the PDO::FETCH_ASSOC fetch style, the returned result for each row does not include the t1 or t2 qualifier as you might expect.   Instead the returned array has a single key, 'id', and a single value.  In order to retrieve both id values, a solution is to use the PDO::FETCH_NUM style.  This returns an array with two keys, 0 and 1, and the two id values.

The SQL shown above returns every permutation of duplicate rows, so every pair of duplicates is reported twice.  To report each duplicate pair only once, change the last comparison to t1.id < t2.id