I would look again at TEXT fields.
From
http://dev.mysql.com/doc/mysql/en/blob.html
... You can have indexes on BLOB and TEXT columns only as of MySQL 3.23.2 for MyISAM tables or MySQL 4.0.14 for InnoDB tables. Older versions of MySQL did not support indexing these column types.
For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. ...
desc txt;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | | PRI | NULL | auto_increment |
| val | text | | | | |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
CREATE INDEX txt_val ON txt (val(1000));
0 rows in set (who knows time)
select count(*) from txt;
+----------+
| count(*) |
+----------+
| 1288262 |
+----------+
1 row in set (0.00 sec)
select id from txt where val = ('1010010101000');
+---------+ | id
| +---------+
| 770046 |
| 972727 |
| 1112534 |
+---------+
3 rows in set (0.04 sec)
mysql> select id from txt where val = ('0100101010010101010101010101010101010101010101010101001010100101010101010101010101010101010101010101010010101001010101010101010101010101010101010101010100101010010101010101010101010101010101010101010101001010100101010101010101010101010101010101010101010010101001010101010101010101010101010101010101010100101010010101010101010101010101010101010101010110100101010010101010101010101010101010101010101010101010111111100');
+---------+
| id |
+---------+
| 1368791 |
+---------+
1 row in set (0.02 sec)
select id from txt where val = ('01001010100101010101010101010101010101010101010101010010101001010101010101010101010101010101010101010100101010010101010101010101010101010101010101010101001010100101010101010101010101010101010101010101010010101001010101010101010101010101010101010101010100101010010101010101010101010101010101010101010101001010100101010101010101010101010101010101010101101001010100101010101010101010101010101010101010101010101111111000');
+---------+
| id |
+---------+
| 1368792 |
+---------+
1 row in set (0.02 sec)
mysql> select id from txt where val = ('1010010101000');
+---------+
| id
| +---------+
| 770046 |
| 972727 |
| 1112534 |
+---------+
3 rows in set (0.04 sec)
mysql> select id from txt where val = ('1010010101000');
+---------+
| id |
+---------+
| 770046 |
| 972727 |
| 1112534 |
+---------+
3 rows in set (0.00 sec)
alter table txt drop trogger txt_key;
mysql> select id from txt where val = ('1010010101000');
+---------+
| id |
+---------+
| 770046 |
| 972727 |
| 1112534 |
+---------+
3 rows in set (3.35 sec)
mysql> select id from txt where val = ('010010101001010101010101010101010101010101010101010100101010010101010101010101010101010101010101010101001010100101010101010101010101010101010101010101010010101001010101010101010101010101010101010101010100101010010101010101010101010101010101010101010101001010100101010101010101010101010101010101010101010010101001010101010101010101010101010101010101011010010101001010101010101010101010101010101010101010101011110000');
Empty set (3.54 sec)