0

I have this table with transactions:

idtransaction(pk)

time

quantity

price_unit

transaction_type (enum buy|sell)

item_iditem(fk)

character_idcharacter(fk)

and I'm trying to retrieve every buy or sell transaction for every character that belongs to a given user (there can be multiple characters for a user account), sorted by date. There is a table 'aggr' that lists every character from every user, like this

'aggr' character_idcharacter(fk), user_iduser(fk)

So naturally, my queries will be (in case of retrieving all sales)

SELECT idtransaction, item_iditem, quantity, time, price_unit
FROM transaction
WHERE transaction_type =  'Sell'
AND character_idcharacter
IN (

SELECT character_idcharacter
FROM aggr
WHERE user_iduser = ( 
SELECT iduser
FROM user
WHERE username =  'testuser' ) 
ORDER BY character_idcharacter
)
ORDER BY time ASC

This takes a long time to perform when I have around ~2 million transactions total (4-5 seconds for around 700 results).

I already tried creating indexes for the character foreign key and in the transaction_type field, but it doesn't seem to improve much. Any tips on how I can make this faster? (either rewriting the query or fiddling with something else on MySQL)

Community
  • 1
  • 1
jonnybravo
  • 137
  • 9
  • I don't like IN sub queries much, maybe this question http://stackoverflow.com/questions/1200295/sql-join-vs-in-performance would help. – Dolfa Apr 29 '16 at 19:01

2 Answers2

1

you can try INNER JOINS instead in of IN to see if it's faster

SELECT t.idtransaction,t.item_iditem,t.quantity,t.time,t.price_unit
FROM transaction t
INNER JOIN aggr a ON a.character_idcharacter = t.character_idcharacter
INNER JOIN user u ON u.iduser = a.user_iduser
WHERE u.username = 'testuser'
AND t.transaction_type = 'Sell'
ORDER BY t.time ASC
Tin Tran
  • 6,194
  • 3
  • 19
  • 34
1

First off I would get rid of the inner "ORDER BY character_idcharacter". If that doesn't do much for performance I would suggest using inner joins rather than subquerys. Something like this... 

SELECT transaction.idtransaction, transaction.item_iditem, transaction.quantity, transaction.time, transaction.price_unit
FROM transaction INNER JOIN aggr on transaction.character_idcharacter = aggr.character_idcharacter
INNER JOIN user on aggr.user_iduser = user.iduser 
WHERE transaction.transaction_type = 'Sell'
 and user.username = 'testuser' 
ORDER BY transaction.time ASC

Noel

Isaac
  • 3,240
  • 2
  • 24
  • 31