indexing - Increase speed of a mySQL query -

i have table 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 list of account daily chargings. if need balance of account use select sum(change_ammount) accounthistory account_id=; quite fast becouse added index on account_id column.

but need find time when account went in minus (date when sum(change_ammount)<0) use query:

select main.date date accounthistory main main.account_id=484368430       , (select sum(change_ammount) accounthistory sub                            sub.account_id=484368430 ,                                  sub.date < main.date)<0 order main.date desc limit 1; 

but works slow. can propose beter solution? maybe need indexes (not on account_id)?

the way make query faster use denormalization: store current account balance on every record. achieve this, you'll have 3 things, we'll @ how query look:

a) add columns table:

alter table accounthistory add balance float; 

b) populate new column

update accounthistory main set balance = (     select sum(change_amount)     accounthistory     account_id = main.account_id     , data <= main.date ); 

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

now query find dattes account changed negative, fast, becomes:

select date accounthistory balance < 0 , balance - change_amount > 0 , account_id = ?;