Hi, I'm trying to learn a little about query optimization.. I have a query that is
pretty slow, and was wondering if there is an easy way to speed it up.
mysql> EXPLAIN
-> SELECT
-> mailfrom,
-> sum((msgsize)/1000000) as mb,
-> count(id) as count,
-> avg(hits) as hits
-> FROM email
-> GROUP BY mailfrom
-> ORDER BY count DESC
-> LIMIT 10;
+-------+------+---------------+------+---------+------+--------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-------+------+---------------+------+---------+------+--------+---------------------------------+
| email | ALL | NULL | NULL | NULL | NULL | 343030 | Using temporary;
|Using filesort |
+-------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.00 sec)
mysql> describe email;
+-----------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra
| |
+-----------------+---------------------+------+-----+---------------------+----------------+
| id | int(12) unsigned | | PRI | NULL |
|auto_increment |
| datetime | datetime | | MUL | 2003-01-01 00:00:00 |
| |
| spam | tinyint(1) unsigned | YES | MUL | 0 |
| |
| virus | tinyint(1) unsigned | YES | MUL | 0 |
| |
| hits | decimal(5,2) | YES | | 0.00 |
| |
| msgsize | int(12) | YES | | 0 |
| |
| mailfrom | varchar(128) | YES | MUL | NULL |
| |
| rcptto | varchar(128) | YES | MUL | NULL |
| |
| subject | varchar(128) | YES | MUL | NULL |
| |
+-----------------+---------------------+------+-----+---------------------+----------------+
runing the query gives takes...
mysql> 10 rows in set (46.45 sec)
I have indexed everything except hits and msgsize, should I index those also, or will
it even matter?
TIA!
Dallas
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
