mysql - How can I impove this JOIN operation between key table and another -

my problem have 2 different queries work in different situations.

schema

  messages        message_id, entity_id, message, timestamp     subscription       user_id, entity_id     users       user_id     entities       entity_id 

situation 1: lots of message entries, , @ least 1 relevant subscription entry

situation 2: few message entries and/or few, or zero, subscription entries relevant

my 2 queries are:

 select messages.*    messages    straight_join subscription on subscription.entity_id = messages.entity_id    subscription.user_id = 1    order messages.timestamp desc     limit 50 

this query works in situation 1 (.000x seconds): lots of message entries, , @ least 1 relevant subscription entry. thisquery take 1.7+ seconds in situation 2.

 select messages.*    messages    inner join subscription on subscription.entity_id = messages.entity_id    subscription.user_id = 1    order messages.timestamp desc     limit 50 

this query works in situation 2 (.000x seconds): few message entries and/or few, or zero, subscription entries relevant. query take 1.3+ seconds in situation 1.

is there query can use can best of both worlds? if not, what's best way handle case?

indexes:

( subscription.user_id, subscription.entity_id ) ( subscription.entity_id ) ( messages.entity_id, messages.timestamp ) ( messages.timestamp ) 

explain info

limit 50

| id | select_type | table             | type   | possible_keys                           | key           | key_len | ref                                    | rows |       | |  1 | simple      | messages          | index  | idx_timestamp                           | idx_timestamp | 4       | null                                   |   50 |             | |  1 | simple      | subscription      | eq_ref | primary,entity_id,user_id               | primary       | 16      | const, messages.entity_id              |    1 | using index | 

without limit

| id | select_type | table             | type   | possible_keys                           | key           | key_len | ref                                    |   rows   |         | |  1 | simple      | messages          |    | entity_id_2,entity_id                   | null          | null    | nul                                    |   255069 | using filesort| |  1 | simple      | subscription      | eq_ref | primary,entity_id,user_id               | primary       | 16      | const, messages.entity_id              |        1 | using index   | 

create table statements:

with ~5000 rows

subscription | create table `subscription` (   `user_id`   bigint(20) unsigned not null,   `entity_id` bigint(20) unsigned not null,   primary key (`user_id`,`entity_id`),   key `entity_id` (`entity_id`) ) engine=innodb default charset=utf8 

with ~255,000 rows

messages | create table `messages` (   `message_id` bigint(20) unsigned not null auto_increment,   `entity_id` bigint(20) unsigned not null,   `message` varchar(255) not null default '',   `timestamp` int(10) unsigned not null,   primary key (`message_id`),   key `entity_id` (`entity_id`,`timestamp`),   key `idx_timestamp` (`timestamp`) ) engine=innodb default charset=utf8  

try changing where and

select messages.*    messages    straight_join subscription on subscription.entity_id = messages.entity_id         , subscription.user_id = 1    order messages.timestamp desc     limit 50 

or

select messages.*    messages    inner join subscription on subscription.entity_id = messages.entity_id            , subscription.user_id = 1    order messages.timestamp desc     limit 50 

or may way :

select messages.* subscription  straight_join messages on subscription.entity_id = messages.entity_id subscription.user_id = 1 order messages.timestamp desc  limit 50