I have an SQL query that sometimes (but certainly not always) appears in my slow query log, and I'm not sure how it can be optimized. Can anyone offer some tips?

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;

Adding an index for the username may help but the biggest part of your problem is a lack of RAM available for mysql operations. The query itself should be rather quick if you can allocate enough memory to the tmp_table_size variable in my.cnf and even faster if you can allocate enough to sort_buffer_size.

I don't think an index on username is all that important, as I only have two usernames

Right now, I have sort_buffer set to 64K. Any suggestions how I can find a better value?

I don't have tmp_table_size 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 tmp_table_size must be to fit this into RAM?

Thanks

Will optimizing the query this way help?

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.

try to use this script to optimize your mysql conf

http://www.day32.com/MySQL/tuning-primer.sh

Thanks, except that (a) I'm already using tuning-primer.sh to adjust my my.cnf (as mentioned above) and (b) the question was about a query, not about the server

Thanks anyway

disregard this post.