I have two tables:

CREATE TABLE `users_transactions` (
    `user_id` int(11) NOT NULL,
    `transaction_id` int(11) NOT NULL,
    UNIQUE KEY `user_id_transaction_id_unique` (`user_id`,`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `transactions` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(45) DEFAULT NULL,
    `transaction_date` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `index_transactions_on_transaction_date` (`transaction_date`)
) ENGINE=InnoDB AUTO_INCREMENT=2000000 DEFAULT CHARSET=utf8

I tried to run this SQL

SELECT `transactions`.`id`, `transactions`.`transaction_date` 
FROM `transactions` 
INNER JOIN `users_transactions` 
ON `transactions`.`id` = `users_transactions`.`transaction_id` 
WHERE `users_transactions`.`user_id` = 71720 
ORDER BY `transactions`.`transaction_date` DESC 
LIMIT 25 OFFSET 0;

User 71720 has more than 27000 transactions, this SQL will cost > 4s. I tried to explain it, and it showed

*************************** 1. row ***************************

id: 1   select_type: SIMPLE   
table: users_transactions   
type: ref possible_keys: user_id_transaction_id_unique    
key: user_id_transaction_id_unique   
key_len: 4  
ref: const    
rows: 52968  <--- It returns too many rows, bad smell 
Extra: Using index; Using temporary; Using file sort

*************************** 2. row ***************************

id: 1   select_type: SIMPLE   
table: transactions    
type: eq_ref possible_keys: PRIMARY    
key: PRIMARY   
key_len: 8    
ref: NULL   
rows: 1    
Extra: Using where 2 rows in set (0.00 sec)

which shows it didn't use index_transactions_on_transaction_date.

While I tried to use STRAIGHT_JOIN, it was using index_transactions_on_transaction_date, and was executed quickly on User 71720. But for other users who don't have many transactions, STRAIGHT_JOIN is much slower than INNER JOIN.

Any suggestions?