jonnybravo jonnybravo - 2 years ago 64
SQL Question

Improving SELECT performance in MySQL

I have this table with transactions:





transaction_type (enum buy|sell)



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

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

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 Source

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


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download