Jay,
I ran into a very similar situation in my application.
Check out my 08/11/2009 reply in thread that starts at:
http://forums.mysql.com/read.php?106,275151,275151#msg-275151
This will work if the ARCHIVE engine will have acceptable performance for other queries in your application.
In my application the column I was hashing on is declared as CHAR(6). I don't know whether a VARCHAR column would work any differently. I would recommend you
PARTITION BY KEY(foobar) rather than creating another column with only the first letter to "PARTITION BY".
The EXPLAIN PARTITIONS command will tell the story, though note it is not meaningful until there are some rows loaded into the table. If it is working correctly, a SELECT on foobar will take you to the exact partition that contains all the rows with the specified value of foobar.
A sample EXPLAIN PARTITIONS from my partitioned table:
EXPLAIN PARTITIONS SELECT * FROM historical_options_18 where ticker = 'AAPL'
--------------
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: historical_options_18
partitions: p235
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 122814313
Extra: Using where
My table currently has 122 million rows and 500 partitions.
Allan