Help with a MySQL query optimization
mysql> explain select max(timestamp) as timestamp, dcdate, px_feeds.id from px_items, px_feeds where px_items.feed_id = px_feeds.id and in_blogroll = '1' and username = 'XXX' group by px_feeds.id order by timestamp;
+----+-------------+----------+------+----------------+-------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+----------------+-------------+---------+-------------------------+------+----------------------------------------------+
| 1 | SIMPLE | px_feeds | ALL | PRIMARY,id_idx | NULL | NULL | NULL | 260 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | px_items | ref | feed_id_idx | feed_id_idx | 4 | mikeage_fof.px_feeds.id | 145 | |
+----+-------------+----------+------+----------------+-------------+---------+-------------------------+------+----------------------------------------------+
My slow query log often says things like:
# Time: 081019 8:28:41
# User@Host: mikeage_fof[mikeage_fof] @ localhost []
# Query_time: 22 Lock_time: 0 Rows_sent: 71 Rows_examined: 10363
use mikeage_fof;
select max(timestamp) as timestamp, dcdate,
px_feeds.id from px_items, px_feeds where
px_items.feed_id = px_feeds.id and in_blogroll = '1' and
username = 'XXX' group
by px_feeds.id order by timestamp;
6 Replies
Right now, I have sort_buffer set to 64K. Any suggestions how I can find a better value?
I don't have tmptablesize set explicitely, but tuning-primer.sh reports:
TEMP TABLES
Current max_heap_table_size = 48 M
Current tmp_table_size = 32 M
Of 13896 temp tables, 20% were created on disk
Created disk tmp tables ratio seems fine
Is there a way I can tell how big tmptablesize must be to fit this into RAM?
Thanks
select max(timestamp)as timestamp ,feed_id as id from px_items where feed_id in (select id from px_feeds where in_blogroll = '1' and username = 'XXX') group by feed_id order by timestamp;
Running from the command line seems to suggest that this is faster, but I don't have the theoretical background to confirm it.
Thanks anyway
