mysql, indexes and list of values

[dominant]

i have a table that have some fields (integer) and they have indexes, as well.

i want to search for a list of values.

the syntax (WHERE field IN (value1,value2,...) doesn;t user the index of the field

Do you know any other method for lists matching?

[devinemke]

if your index is set properly then your query should use it with the IN() syntax. if i have a table such as

CREATE TABLE`test` (
  `num` tinyint(4) DEFAULT NULL,
  KEY `num_index` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test` (`num`) VALUES
(1),
(1),
(2),
(3),
(4);

then if i run the following query:

EXPLAIN SELECT num FROM test WHERE num IN(1,2)

i get the following result:

id: 1
select_type: SIMPLE
table: test
type: range
possible_keys: num_index
key: num_index
key_len: 2
ref: NULL
rows: 3
Extra: Using where; Using index

[dominant]

in my ocasion the index is properly set in a filed that is not a primary key or something, but in explain there is null in key column