1

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?

Improve this question

2 Answers 2

Reset to default
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:

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Sorry, but I don't quite understand different execution plans. Why there is difference between big data and small data? Thank you very much!
@Prajna . . . If I understand correctly, there isn't a difference and that is the problem. You want there to be a difference, because one plan works better on some data than others.
0

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

Improve this answer

3 Comments

No, it doesn't work. Both INNER JOIN and STRAIGHT_JOIN will eventually use index_transactions_on_transaction_date , separate indices only appear in possible keys. Thanks anyway.
index on date will not be beneficial for you as you are not filtering any data based on date....here best way will be that put a condition on date as you are just trying to get latest 25 records..check updated query in my answer...
Seems to me it's almost the same ;(

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.