MYSQL: Get "double" max of row with group by

My Table:

id, user_id, street, street_number, street_stairs, street_door, zip, place, country, type

Id is the primary key. Type can be 0 or 1. Every user_id can have several address-rows.

CREATE TABLE `user_address_data` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `street` varchar(55) NOT NULL,
 `street_number` varchar(5) NOT NULL,
 `street_stairs` varchar(3) DEFAULT NULL,
 `street_door` varchar(3) DEFAULT NULL,
 `zip` varchar(10) NOT NULL,
 `place` varchar(30) NOT NULL,
 `country` varchar(2) NOT NULL,
 `type` tinyint(2) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `user_id` (`user_id`,`street`,`street_number`,`street_stairs`,`street_door`,`zip`,`place`,`country`)
) ENGINE=MyISAM AUTO_INCREMENT=192 DEFAULT CHARSET=utf8

T开发者_StackOverflowhe Query:

SELECT `t0`.`user_id`, `t0`.`country`
        FROM `user_address_data` as `t0` 
        INNER JOIN (SELECT `t1`.`user_id`, MAX(`t1`.`id`) as `id`
                    FROM `user_address_data` as `t1`
                    RIGHT JOIN (SELECT `user_address_data`.`user_id`, MAX(`user_address_data`.`type`) as `type`
                                FROM `user_address_data`
                                WHERE `user_address_data`.`user_id` IN (42,4, 20, 41, 43, 171)
                                GROUP BY `user_address_data`.`user_id`) as `t2`
                        ON `t2`.`user_id` = `t1`.`user_id` && `t2`.`type` = `t1`.`type`
                    GROUP BY `t1`.`user_id`) as `t3`
        ON `t3`.`id` = `t0`.`id`

Question: As you can see, I'm trying to get the latest row from the address table. However I need the latest row where the field "type" represents the max type of this user_id.

My idea was to select the max(type) per user_id first. Therefore I was able to select the max(id) of every user. Now I'm able to select the corresponding Country code from the field country.

It works, however I don't think it is efficient. There are 2 temporary tables that have to be created. Are there any other ways of doing this more efficiently?

Thanks.

as requested, the EXPLAIN SELECT:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    6    
1   PRIMARY     t0  eq_ref  PRIMARY     PRIMARY     4   t3.id   1    
2   DERIVED     <derived3>  ALL     NULL    NULL    NULL    NULL    6   Using temporary; Using filesort
2   DERIVED     t1  ref     user_id     user_id     4   t2.user_id  11   
3   DERIVED     user_address_data   range   user_id     user_id     4   NULL    20  Using where

I don't know if you will be getting an unexpected "Type" in the data for type of address.... and I don't know what the address "Types" represent. Take the following scenario for a single user.

User  Address ID    Type of Address
1      1            8
1      2            3
1      3            1
1      4            0

if you are getting the max address ID (in this case, 4), and the max type of address (8 from Address ID = 1), you might result in incorrect data. That said, if the primary consideration is that of the most recent Address ID, then you never need to care about the max() type of address as there would only be one record associated with the max( Address ID )... So, if you want the last address ID based on the highest type of address, I would go with two-subqueries as you have so described ( resulting in Address ID = 1, Type = 8). However if you are looking for (Address = 4, Type = 0 since that is the latest address for a person, I would revise it).

Let us know.

select