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)
www.hellburner.net | Party! | uin: 47753803

Re: effektivare query

Se till att det finns index på de kolumner du joinar "runt". I det här fallet dataid i party_replay. Du skulle kunna prova att sätta ett index på type också, men i så fall kanske bara på ett par tecken med tanke på att ett index på alla 16 kan bli ganska stort.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

3 (edited by gribber 2003-03-13 22:45)

Re: effektivare query

satan i gatan, vilken skillnad smile

från 0.55 sec på en dual 400 till 0.05 smile

man tackar!

edit: testade att sätta index på type också, men då steg tiden 0.02 sek

www.hellburner.net | Party! | uin: 47753803

Re: effektivare query

Trevligt :-)

"Programming is like sex: one mistake and you have to support it for the rest of your life."