INNER JOIN or STRAIGHT_JOIN

Question

I have two tables:

| users_transactions | 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 |

| transactions | 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?

Answer 1

This is your query:

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

You have a problem where you have two different execution plans, and one works best for some data and one works best for others. I don't think MySQL does a good job handling this.

Is there any way that you can put a transaction_date column into user_transactions? That would give you the ability to optimize the query for both cases.

I would suggest rewriting it as:

Answer 2

try below query-

SELECT t.id, t.transaction_date
FROM transactions t INNER JOIN
     users_transactions ut
     ON t.id = ut.transaction_id
WHERE ut.user_id = 71720 
and t.transaction_date >= subdate(curdate(),interval 30 day) 
ORDER BY t.transaction_date DESC
LIMIT 25;

Note: Assuming this user have at least 25 records in last 30 days or you can increase this window...