Optimizing a Complex Query / Removing 'Using Filesort' and 'Using temporary'

[eonsol]

Hello.

 

I am using MySQL server: 4.1.22-standard-log

 

Raw SQL statement in question:

 

 SELECT Pictures.id, Pictures.username, max( Pictures.timestamp ) AS max_timestamp
FROM Pictures, Friends
WHERE (
Pictures.username = Friends.friend
AND Friends.name = "joe"
)
GROUP BY Pictures.username
ORDER BY max_timestamp DESC
LIMIT 5

 

Tables:

 

CREATE TABLE `Pictures` (
  `id` mediumint(9) NOT NULL auto_increment,
  `username` varchar(42) NOT NULL default '',
  `name` varchar(64) NOT NULL default '',
  `defaultpic` char(1) NOT NULL default '',
  `description` varchar(64) NOT NULL default '',
  `comments` smallint(6) NOT NULL default '0',
  `order` double(4,1) NOT NULL default '0.0',
  `lastcomment` int(11) NOT NULL default '0',
  `lastchecked` int(11) NOT NULL default '0',
  `folder` varchar(128) NOT NULL default '',
  `timestamp` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `order` (`order`),
  KEY `username` (`username`(10)),
  KEY `timestamp` (`timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=1694311 DEFAULT CHARSET=latin1

  CREATE TABLE `Friends` (
  `ID` mediumint(9) NOT NULL auto_increment,
  `name` varchar(42) NOT NULL default '',
  `friend` varchar(42) NOT NULL default '',
  PRIMARY KEY  (`ID`),
  KEY `name` (`name`(10)),
  KEY `friend` (`friend`(10))
) ENGINE=MyISAM AUTO_INCREMENT=1187262 DEFAULT CHARSET=latin1 

 

Explain query:

 

+----+-------------+----------+------+---------------+----------+---------+---------------------------+------+----------------------------------------------+
| id | select_type | table    | type | possible_keys | key      | key_len | ref                       | rows | Extra                                        |
+----+-------------+----------+------+---------------+----------+---------+---------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | Friends  | ref  | name,friend   | name     |      10 | const                     |   43 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Pictures | ref  | username      | username |      10 | dark_users.Friends.friend |    8 | Using where                                  |
+----+-------------+----------+------+---------------+----------+---------+---------------------------+------+----------------------------------------------+

 

What I am doing with this statement:

 

This database is for a social networking website where the users add friends and also upload pictures.  If "Joe" adds "Sally" as a friend, then a Friends record would be created where name = "Joe" and friend = "Sally".  If Sally uploads a picture, a Pictures record would be created where username = "Sally" and timestamp = [the current unix timestamp].

 

Therefore, this query will show us the 5 most recent picture records which were created by Joe's friends.  Additionally, it will only list one record per friend (showing only the most recently created record by that friend).

 

The query works fine and executes very fast (probably because of the 'LIMIT 5'), but I would like to streamline this as much as possible since I will be using similar logic on many other tables.  So I'm appealing to the wisdom of this community.  If anyone has suggestions to re-structure the query or the indexes to optimize this sort of thing, that would be great.

 

In particular, I cannot figure out why EXPLAIN says: "Using temporary; Using filesort".  Obviously, this is because of the 'ORDER BY' section of the query.  However, the column that I'm ordering by has been indexed, so I do not understand why MySQL resorts to filesort.  In the least, I would like to eliminate this.

 

Thank you!

 

[fenway]

You're ordering by an expression.

[eonsol]

Yes, but since the expression derives it's result from an indexed column, I'd hoped to skip the filesort.

 

Also, I tried variations on this query without the expression and was still stuck with filesort. 

 

For example:

 

 SELECT Pictures.id, Pictures.username, Pictures.timestamp
FROM Pictures, Friends
WHERE (
Pictures.username = Friends.friend
AND Friends.name = "joe"
)
ORDER BY Pictures.timestamp DESC
LIMIT 5 

 

Explain:

 

+----+-------------+----------+------+---------------+----------+---------+---------------------------+------+----------------------------------------------+
| id | select_type | table    | type | possible_keys | key      | key_len | ref                       | rows | Extra                                        |
+----+-------------+----------+------+---------------+----------+---------+---------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | Friends  | ref  | name,friend   | name     |      10 | const                     |   78 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Pictures | ref  | username      | username |      10 | dark_users.Friends.friend |    8 | Using where                                  |
+----+-------------+----------+------+---------------+----------+---------+---------------------------+------+-------------------------------

 

The above is more of an experiment, really, because I do need to group the results.  But it does show that there must be something wrong with the basic idea of the query (or the indexes) as far as sorting goes.

 

Any suggestions?

 

[fenway]

Well, you can use a covering index on  both columns, but that's only going to get you so far.