jonnybravo jonnybravo - 7 months ago 10
SQL Question

Improving SELECT performance in MySQL

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)

Answer

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

Comments