Increase speed of a mySQL query

Question

I have a table like this.

CREATE TABLE `accounthistory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime DEFAULT NULL,
  `change_ammount` float DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
)

Its a list of account daily chargings. If i need the balance of the account i use SELECT sum(change_ammount) FROM accounthistory WHERE account_id=; Its quite fast becouse i added an index on the account_id column.

But now i need to find the time when the account went in minus (date when SUM(change_ammount)<0) I use this query:

SELECT main.date as date from accounthistory as main
WHERE main.account_id=484368430
      AND (SELECT sum(change_ammount) FROM accounthistory as sub
                           WHERE sub.account_id=484368430 AND
                                 sub.date < main.date)<0
ORDER BY main.date DESC
LIMIT 1;

But it works very slow. Can you propose a beter solution? Maybe i need some indexes (not only on account_id)?

score 1 · Answer 1 · answered Jun 30 '11 at 20:45

The way to make your query faster is to use denormalization: Store the current account balance on every record. The achieve this, you'll have to do three things, then we'll look at how the query would look:

a) Add a columns to your table:

ALTER TABLE accounthistory ADD balance float;

b) Populate the new column

UPDATE accounthistory main SET
balance = (
    SELECT SUM(change_amount)
    FROM accounthistory
    where account_id = main.account_id
    and data <= main.date
);

c) To populate new rows, either a) use a trigger, b) use application logic, or c) run the above UPDATE statement for the row added after adding it, ie UPDATE ... WHERE id = ?

Now the query to find which dattes the account changed to negative, which will be very fast, becomes:

SELECT date
from accounthistory
where balance < 0
and balance - change_amount > 0
and account_id = ?;

Thanks. I will try denormalization. – Taras Jul 01 '11 at 07:25 — Taras, Jul 01 '11 at 07:25

score 0 · Answer 2 · answered Jun 30 '11 at 18:38

SELECT MAX(main.date) as date 
from accounthistory as main
WHERE main.account_id=484368430
      AND EXISTS (SELECT 1 FROM accounthistory as sub
                           WHERE sub.account_id=main.account_id AND
                                 sub.date < main.date HAVING SUM(sub.change_ammount) < 0)

Increase speed of a mySQL query

2 Answers2