MyISAM vs InnoDB - use case

[kid_drew]

Hey everybody,

I have a site that is pushing 1M users with ~2M pageviews per day.  It's built on a form of artificial currency and involves a lot of transactions of this artificial currency.  I log every monetary transaction into one table that, as you can imagine, is rather large (32M rows).  I move old transactions daily into a non-live table to keep the size manageable, but here's my issue.  I intentionally didn't build indexes in this table because it is only ever read from the MySQL monitor.  It is only written to by users, so read does not need to be quick.  The problem is that when I do need to read it (maybe twice a day) it takes FOREVER to query because it has to go through every row.  So I'm wondering two things:

 

1.  Should this table be InnoDB or MyISAM?

2.  How much of a performance hit will I take if I build indexes on the table?

[fenway]

What's your table structure? What type of queries are you running?

[kid_drew]

Only inserts.

[fenway]

Yes, but what queries are you running when are you doing them?

[kid_drew]

Do you mean when I run them as root?  Only select/where queries.  select * from trans where....

[fenway]

Do you mean when I run them as root?  Only select/where queries.  select * from trans where....

Yes, where what? What's the table structure?

[kid_drew]

select * from trans where user_id=xxxxxx is very typical.  Sometimes I query for the transaction type too.  So select * from trans where user_id=xxxxxx and trans_type=xx.

[fenway]

yeah, it's going to be painful to do this without an index.  I would suggest a covering index on (user_id, trans_type) -- it will take forever to build this the first time, but there shouldn't be too much of a performance hit during normal operations... how many insert/s?

[kid_drew]

A few million a day.

[fenway]

That's about 30/s... really?  Can you afford to use INSERT DELAYED? If so, the performance hit for maintaining the index shouldn't be noticable.

[kid_drew]

I've never used INSERT DELAYED actually, so I'm not sure if I can afford it or not.  As I understand it, MySQL will bundle a bunch of inserts together to throw in at one time.  Seems that might actually speed things up.

[fenway]

I've never used INSERT DELAYED actually, so I'm not sure if I can afford it or not.  As I understand it, MySQL will bundle a bunch of inserts together to throw in at one time.  Seems that might actually speed things up.

It only matters if you're going to using the UID you get back from the insert immediately; or it could be delayed a while if there was a read lock (but that would happen anyway even with a regular insert).