gistfile1.txt

mysql> describe urls;                                                                                                      
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |                                                         
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |                                                         
| url     | text         | NO   |     | NULL    |                |
| userKey | varchar(255) | YES  |     | NULL    |                |                                                         
| userIp  | varchar(255) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+                                                         
4 rows in set (0.00 sec)
                                                                                                                           
mysql> describe clicks;
+------------+--------------+------+-----+---------+----------------+                                                      
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+                                                      
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| url_id     | int(11)      | NO   | MUL | NULL    |                |                                                      
| click_date | datetime     | YES  |     | NULL    |                |
| ip         | varchar(255) | YES  |     | NULL    |                |                                                      
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)    


Statement:

SELECT urls.id, urls.url, count(clicks.id) as click_count FROM urls LEFT JOIN clicks ON urls.id = clicks.url_id WHERE urls.userKey = '$userKeyEscaped' GROUP BY clicks.url_id ORDER BY urls.id DESC LIMIT 25