Am 02.02.2012 07:04, schrieb fp: >> sorry for the dumb question but how can i get the untruncated? >> this is from "show processlist" and had also the feeling that it >> may be truncated but phpMyAdmin showed the same > > Via ssh it is: > # mysql -e "show full processlist"
well, everytime i learn new things even after 10 years in business
did not know "show full" before in this context
below the explain and the full killer-query
SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts k LEFT JOIN
dbmail_partlists l ON k.id=l.part_id LEFT JOIN
dbmail_physmessage p ON l.physmessage_id=p.id LEFT JOIN dbmail_header h ON
h.physmessage_id=p.id LEFT JOIN
dbmail_headervalue v ON h.headervalue_id=v.id LEFT JOIN dbmail_messages m ON
m.physmessage_id=p.id WHERE
m.mailbox_idnr = 1406 AND m.status IN (0,1) AND v.headervalue LIKE
'%charsettest%' OR k.data LIKE BINARY
'%charsettest%' ORDER BY m.message_idnr;
mysql> explain SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts k LEFT JOIN
dbmail_partlists l ON
k.id=l.part_id LEFT JOIN dbmail_physmessage p ON l.physmessage_id=p.id LEFT
JOIN dbmail_header h ON
h.physmessage_id=p.id LEFT JOIN dbmail_headervalue v ON h.headervalue_id=v.id
LEFT JOIN dbmail_messages m ON
m.physmessage_id=p.id WHERE m.mailbox_idnr = 1406 AND m.status IN (0,1) AND
v.headervalue LIKE '%charsettest%' OR
k.data LIKE BINARY '%charsettest%' ORDER BY m.message_idnr;
+----+-------------+-------+--------+-----------------------------------------------------------------------------------+----------------------+---------+-------------------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows |
Extra |
+----+-------------+-------+--------+-----------------------------------------------------------------------------------+----------------------+---------+-------------------------+---------+---------------------------------+
| 1 | SIMPLE | k | ALL | NULL
| NULL | NULL | NULL | 1712428 |
Using temporary; Using filesort |
| 1 | SIMPLE | l | ref | part_id
| part_id | 8 | dbmail.k.id | 1 |
Using index |
| 1 | SIMPLE | p | eq_ref | PRIMARY
| PRIMARY | 8 | dbmail.l.physmessage_id | 1 |
Using index |
| 1 | SIMPLE | h | ref |
PRIMARY,physmessage_id,physmessage_id_headername_id,physmessage_id_headervalue_id
| physmessage_id | 8
| dbmail.p.id | 8 | Using index |
| 1 | SIMPLE | v | eq_ref | PRIMARY
| PRIMARY | 8 | dbmail.h.headervalue_id | 1 |
Using where |
| 1 | SIMPLE | m | ref | physmessage_id_index
| physmessage_id_index | 8 | dbmail.p.id | 1 |
Using where |
+----+-------------+-------+--------+-----------------------------------------------------------------------------------+----------------------+---------+-------------------------+---------+---------------------------------+
6 rows in set (0.00 sec)
signature.asc
Description: OpenPGP digital signature
_______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
