MySQL Forums
Forum List  »  Memory Storage Engine

index not used
Posted by: Marco Saba
Date: February 20, 2008 06:08AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
index not used
8783
February 20, 2008 06:08AM
5364
February 20, 2008 04:13PM
5026
February 21, 2008 03:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.