Topic: effektivare query
hur gör jag denna query effektivare, om de nu går.
SELECT t1.*, COUNT(t2.id) AS comments FROM party_data AS t1 LEFT JOIN party_replay AS t2 ON t1.id = t2.dataid WHERE type = 'pic' GROUP BY t1.id ORDER BY RAND(NOW()) LIMIT 4
lite data:
explain
+-------+------+---------------+------+---------+------+------+---------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+---------------------------------------------+
| t1 | ALL | NULL | NULL | NULL | NULL | 921 | where used; Using temporary; Using filesort |
| t2 | ALL | NULL | NULL | NULL | NULL | 139 | |
+-------+------+---------------+------+---------+------+------+---------------------------------------------+
2 rows in set (0.00 sec)mysql> show columns from party_data;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(128) | | | | |
| descript | varchar(250) | | | | |
| dir | varchar(64) | | | 0 | |
| type | varchar(16) | | | | |
| partyid | int(11) | | | 0 | |
| klick | int(11) | | | 0 | |
+----------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)mysql> show columns from party_replay;
+-------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| text | text | | | | |
| nick | varchar(64) | | | | |
| remote_addr | varchar(15) | | | 0.0.0.0 | |
| timestamp | timestamp(14) | YES | | NULL | |
| dataid | int(11) | | | 0 | |
+-------------+---------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)