MySQL Forums
Forum List  »  Optimizer & Parser

is HASH index being ignored
Posted by: Michal Carson
Date: March 07, 2006 10:17PM

Using MySQL 4.1.15, I've created a MEMORY table with an index on two FLOAT columns like this:

CREATE TABLE `mp`.`mem_test` (
`caID` int( 10 ) unsigned NOT NULL,
`caTitle` varchar( 50 ) default NULL ,
`caLatitude` float default '0',
`caLongitude` float default '0',
KEY `search1` ( `caLatitude` , `caLongitude` )
)
ENGINE = MEMORY DEFAULT CHARSET = latin1;

Since it's a MEMORY table, the index type is defaulting to HASH. I load it with ~500,000 rows from another table. Now the index looks like this:

*************************** 1. row ***************************
Table: mem_test
Non_unique: 1
Key_name: search1
Seq_in_index: 1
Column_name: caLatitude
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: HASH
Comment:
*************************** 2. row ***************************
Table: mem_test
Non_unique: 1
Key_name: search1
Seq_in_index: 2
Column_name: caLongitude
Collation: NULL
Cardinality: 7391
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: HASH
Comment:
2 rows in set (0.00 sec)

When I run this query

explain SELECT caID, caTitle, caLongitude, caLatitude
FROM mem_test
WHERE caLatitude BETWEEN 35.1342 AND 35.8583
AND caLongitude BETWEEN -98.1771 AND -97.2877;

I get this result:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mem_test
type: ALL
possible_keys: search1
key: NULL
key_len: NULL
ref: NULL
rows: 517387
Extra: Using where
1 row in set (0.00 sec)

I get the same result if I FORCE INDEX(`search1`).

If I drop the table and recreate it with a BTREE index, the same explain query gives this result:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mem_test
type: range
possible_keys: search1
key: search1
key_len: 10
ref: NULL
rows: 32380
Extra: Using where
1 row in set (0.00 sec)

and the index looks like this:

*************************** 1. row ***************************
Table: mem_test
Non_unique: 1
Key_name: search1
Seq_in_index: 1
Column_name: caLatitude
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: mem_test
Non_unique: 1
Key_name: search1
Seq_in_index: 2
Column_name: caLongitude
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
2 rows in set (0.01 sec)

So...is MySQL intentionally ignoring the index? There are only 6767 rows that match the where criteria out of 500,000 rows (49 out of 7416 distinct combinations). There are only 39,399 rows that match the latitude criterion. Do these all hash into an indistinguishable jumble? Or is something else wrong here?

Thanks for your help.

Michal

Options: ReplyQuote


Subject
Views
Written By
Posted
is HASH index being ignored
3740
March 07, 2006 10:17PM
2083
March 08, 2006 04:32AM
1923
March 08, 2006 07:50PM
2078
March 08, 2006 10:59AM


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.