Huge Table - Slow query - Can I index better?

[Rob Higgins]

I am building a table that will get large nightly inserts. I need to assume that the table will grow to 20M rows in yr 1, 100M rows in yr 2, 240M rows in yr 3, 470M rows in yr 4, 800M rows in yr 5.

I've bench tested a typical select statement against the table with 10M rows and it's slow. Before I start digging into the documentation I thought I'd see if anyone has any suggestions. .

Here's the table

CREATE TABLE `transactions` (
`transaction_id` int(11) NOT NULL auto_increment,
`transaction_date` datetime default NULL,
`fk_store_id` smallint(6) default NULL,
`fk_storeAcct_id` bigint(20) default NULL,
`fk_cust_id` bigint(20) default NULL,
`cart_total` decimal(6,2) default NULL,
PRIMARY KEY (`transaction_id`),
KEY `ix_fk_store_id` USING BTREE (`fk_store_id`,`transaction_id`),
KEY `ix_fk_cust_id` USING BTREE (`fk_cust_id`,`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

assume the store_id's range from 1-1000
and the cust_id's range from 1-100000

a typical select against this table would look like:

select * from transactions where
(fk_storeAcct_id=3899 and fk_store_id=147) or
(fk_storeAcct_id=35394 and fk_store_id=64) or
(fk_storeAcct_id=50619 and fk_store_id=438)
or
(fk_cust_id=26584)

The EXPLAIN is:
1, 'SIMPLE', 'transactions', 'index_merge', 'ix_fk_storeAcct_id,ix_fk_cust_id', 'ix_fk_storeAcct_id,ix_fk_cust_id', '9,9', '', 549, 'Using sort_union(ix_fk_ storeAcct _id,ix_fk_ cust _id); Using where'

Like I said, I’ve seeded the table with 10M rows and I’m not real happy with the execute time of that select statement. As I write this, I’m adding another 10M to the table and I’m guessing it’s going to be pretty ugly.

Any suggestions regarding the indexes would be much appreciated. The query is a fairly simple one, but if anyone has an idea of how to make it better, please let me know.