tag cloud with mysql

Question

Is there any other way to make tag cloud from mysql without temporary tables?

mysql> explain wallpaper_keywords;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | mediumint(7) unsigned | NO   | PRI | NULL    | auto_increment |
| wallpaper_id | mediumint(7) unsigned | YES  | MUL | NULL    |                |
| keyword_id   | smallint(5) unsigned  | YES  | MUL | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

mysql> explain keywords;
+---------+-----------------------+------+-----+---------+----------------+
| Field   | Type                  | Null | Key | Default | Extra          |
+---------+-----------------------+------+-----+---------+----------------+
| id      | mediumint(7) unsigned | NO   | PRI | NULL    | auto_increment |
| keyword | varchar(32)           | YES  | UNI | NULL    |                |
+---------+-----------------------+------+-----+---------+----------------+

SELECT k.keyword,count(k.keyword) AS count FROM keywords k Left Join wallpaper_keywords wk ON wk.keyword_id = k.id GROUP BY wk.keyword_id, k.keyword ORDER BY count DESC limit 100;

+----------+-------+
| keyword  | count |
+----------+-------+
| computer |    10 |
| road     |     4 |
| tree     |     4 |
| window   |     4 |
| nature   |     4 |
| forest   |     3 |
| cars     |     3 |
| mazda    |     3 |
| abstract |     3 |
| funny    |     3 |
| sport    |     3 |
| sea      |     3 |
| city     |     3 |
| warrior  |     2 |
| bird     |     2 |
| lights   |     1 |
| summer   |     1 |
| space    |     1 |
| dusk     |     1 |
| ubuntu   |     1 |
+----------+-------+

explain:

+----+-------------+--------------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table              | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+--------------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY     | keywords           | index | NULL          | keyword | 99      | NULL |   20 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY     |          | ALL   | NULL          | NULL    | NULL    | NULL |   54 |                                              |
|  2 | DERIVED     | wallpaper_keywords | index | NULL          | PRIMARY | 3       | NULL |   54 |                                              |
+----+-------------+--------------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
3 rows in set (0.00 sec)

EDIT another query: SELECT count,k.keyword from (SELECT * , count(wk.keyword_id) AS count FROM wallpaper_keywords wk GROUP BY wk.keyword_id LIMIT 50000) as data left join keywords as k on k.id = data.keyword_id order by count desc limit 500

+----+-------------+------------+--------+---------------+------------+---------+-----------------+------+----------------+
| id | select_type | table      | type   | possible_keys | key        | key_len | ref             | rows | Extra          |
+----+-------------+------------+--------+---------------+------------+---------+-----------------+------+----------------+
|  1 | PRIMARY     |  | ALL    | NULL          | NULL       | NULL    | NULL            |   20 | Using filesort |
|  1 | PRIMARY     | k          | eq_ref | PRIMARY       | PRIMARY    | 3       | data.keyword_id |    1 |                |
|  2 | DERIVED     | wk         | index  | NULL          | keyword_id | 3       | NULL            |   59 |                |
+----+-------------+------------+--------+---------------+------------+---------+-----------------+------+----------------+

the question would be now - is there any better solution? cheaper (SHOW STATUS LIKE 'last_query_cost';) way to count it?

yAnTar yAnTar · Accepted Answer · 2012-11-13T14:03:20

For counting total value for every keyword you cau use additional field (total_count) in table keywords and by every adding new keyword add +1 to this field - it's really simpler for your database in future, when you will have 100 000 records.

tag cloud with mysql

1 Answers