index not used

[Marco Saba]

Can someone explain why the memory storage engine doesn't use the index? The myisam engine uses the index. This makes the memory storage engine slower than the myisam.

See explain of these to tables:
mysql> explain SELECT SQL_NO_CACHE sum(aantal) FROM mem_count_tmp_xy WHERE ( ( Postalcode BETWEEN '2000AA' AND '2037ZZ' OR Postalcode BETWEEN '2063AA' AND '2063ZZ' OR Postalcode BETWEEN '9300AA' AND '9367ZZ' OR Postalcode BETWEEN '9470AA' AND '9485ZZ' OR Postalcode BETWEEN '9490AA' AND '9491ZZ' OR Postalcode BETWEEN '9493AA' AND '9497ZZ' OR Postalcode BETWEEN '9600AA' AND '9629ZZ' OR Postalcode BETWEEN '9700AA' AND '9845ZZ' OR Postalcode BETWEEN '9860AA' AND '9866ZZ' OR Postalcode BETWEEN '9880AA' AND '9939ZZ' OR Postalcode BETWEEN '8508AA' AND '8508ZZ' OR Postalcode BETWEEN '8529AA' AND '8539ZZ' OR Postalcode BETWEEN '8550AA' AND '8633ZZ' OR Postalcode BETWEEN '8650AA' AND '8724ZZ' OR Postalcode BETWEEN '8741AA' AND '8775ZZ' OR Postalcode BETWEEN '8821AA' AND '8823ZZ' OR Postalcode BETWEEN '8400AA' AND '8409ZZ' OR Postalcode BETWEEN '8420AA' AND '8435ZZ' OR Postalcode BETWEEN '8497AA' AND '8497ZZ' OR Postalcode BETWEEN '9200AA' AND '9223ZZ' OR Postalcode BETWEEN '9240AA' AND '9249ZZ' ) );
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | mem_count_tmp_xy | ALL | PostalCode | NULL | NULL | NULL | 2696696 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT SQL_NO_CACHE sum(aantal) FROM count_tmp_xy WHERE ( ( Postalcode BETWEEN '2000AA' AND '2037ZZ' OR Postalcode BETWEEN '2063AA' AND '2063ZZ' OR Postalcode BETWEEN '9300AA' AND '9367ZZ' OR Postalcode BETWEEN '9470AA' AND '9485ZZ' OR Postalcode BETWEEN '9490AA' AND '9491ZZ' OR Postalcode BETWEEN '9493AA' AND '9497ZZ' OR Postalcode BETWEEN '9600AA' AND '9629ZZ' OR Postalcode BETWEEN '9700AA' AND '9845ZZ' OR Postalcode BETWEEN '9860AA' AND '9866ZZ' OR Postalcode BETWEEN '9880AA' AND '9939ZZ' OR Postalcode BETWEEN '8508AA' AND '8508ZZ' OR Postalcode BETWEEN '8529AA' AND '8539ZZ' OR Postalcode BETWEEN '8550AA' AND '8633ZZ' OR Postalcode BETWEEN '8650AA' AND '8724ZZ' OR Postalcode BETWEEN '8741AA' AND '8775ZZ' OR Postalcode BETWEEN '8821AA' AND '8823ZZ' OR Postalcode BETWEEN '8400AA' AND '8409ZZ' OR Postalcode BETWEEN '8420AA' AND '8435ZZ' OR Postalcode BETWEEN '8497AA' AND '8497ZZ' OR Postalcode BETWEEN '9200AA' AND '9223ZZ' OR Postalcode BETWEEN '9240AA' AND '9249ZZ' ) );
+----+-------------+--------------+-------+---------------+------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+------------+---------+------+--------+-------------+
| 1 | SIMPLE | count_tmp_xy | range | PostalCode | PostalCode | 9 | NULL | 170421 | Using where |
+----+-------------+--------------+-------+---------------+------------+---------+------+--------+-------------+



Edited 1 time(s). Last edit at 02/20/2008 06:09AM by Marco Saba.