php - 用mysql标记云

Question

有没有其他方法可以在没有临时表的情况下从mysql制作标签云？

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 限制 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)

编辑另一个查询: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 限制 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 |                |+----+-------------+------------+--------+---------------+------------+---------+-----------------+------+----------------+

现在的问题是 - 有没有更好的解决方案？更便宜(SHOW STATUS LIKE 'last_query_cost';)的计算方式？

Answer 1

为了计算每个关键字的总值(value)，您可以在表关键字中使用附加字段 (total_count)，并通过每次添加新关键字向该字段添加 +1 - 将来当您拥有 100 000 条记录时，这对您的数据库来说真的很简单.