Seriously, WTF?

[Christopher Jones]

Mentions of MySQL versions and/or scalability (or lack of it) are of  
great interest to me..... Or rather, significantly better (free)  
alternatives would be of great interest to me!

I have a relatively simple table with approximately 100 million rows  
of data, and query times suck. I'd be interested to know whether  
anyone thinks this is good, acceptable/normal, or bad;


+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| read_id  | int(11)          |      | PRI | NULL    | auto_increment |
| chr      | varchar(4)       |      | MUL |         |                |
| location | int(10) unsigned |      |     | 0       |                |
| tally    | int(10) unsigned |      |     | 0       |                |
| histone  | varchar(20)      | YES  | MUL | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

With MySQL 4.0.20 running on Apple XServe dual G5 2.5GHz, 2Gb RAM;

mysql> select * from histones where chr='1' and location > 10000 and  
location < 20000;
+----------+-----+----------+-------+----------+
| read_id  | chr | location | tally | histone  |
+----------+-----+----------+-------+----------+
|        5 | 1   |    18400 |     1 | H3K4me1  |
|  3976081 | 1   |    18385 |     2 | H3K4me2  |
|  6920901 | 1   |    18395 |     1 | H3K4me3  |
|  6920902 | 1   |    19195 |     1 | H3K4me3  |
| 42553314 | 1   |    18295 |     1 | H3K36me1 |
| 84619522 | 1   |    18461 |     1 | H4K20me3 |
| 96397458 | 1   |    18325 |     2 | H4K20me1 |
+----------+-----+----------+-------+----------+
7 rows in set (3 min 39.94 sec)

With MySQL 5.0.51b running on Apple dual G5 2.5GHz, 4Gb RAM;

mysql> select * from histones where chr='1' and location > 10000 and  
location < 20000;
+----------+-----+----------+-------+----------+
| read_id  | chr | location | tally | histone  |
+----------+-----+----------+-------+----------+
|        5 | 1   |    18400 |     1 | H3K4me1  |
|  3976081 | 1   |    18385 |     2 | H3K4me2  |
|  6920901 | 1   |    18395 |     1 | H3K4me3  |
|  6920902 | 1   |    19195 |     1 | H3K4me3  |
| 42553314 | 1   |    18295 |     1 | H3K36me1 |
| 84619522 | 1   |    18461 |     1 | H4K20me3 |
| 96397458 | 1   |    18325 |     2 | H4K20me1 |
+----------+-----+----------+-------+----------+
7 rows in set (2 min 3.43 sec)