use index for IN()

[jaymc]

I have a query which is something like

 

 

SELECT username FROM members WHERE email IN('email1@test.com','email2@test.com','email15@test.com','email3@test.com','email4@test.com')

There are actually 1000 emails

 

The query is taking around 5 seconds, there are 80,000 records in members.

 

I created an index on the email but it wont use it. I even tried to FORCE INDEX but when using EXPLAIN it tells me its just using WHERE without any index

 

How can I optimise this query using an INDEX or something else..

 

EDIT

Ive just change SELECT username to SELECT email and EXPLAIN now says its using the index. However, the query took 20 seconds even with the index

[fenway]

Which version of MySQL are you using?  It won't use the index because it's an OR.

[jaymc]

5.0.54

 

How can I restructure this type of query where I have 1000 records I need to search for in one go?

[fenway]

Post the table structure and EXPLAIN output... I'm looking for index_merge.

[jaymc]

Structure

CREATE TABLE `msn` (

`username` varchar(20) collate latin1_general_ci NOT NULL default '',

`memberemail` varchar(90) collate latin1_general_ci NOT NULL default '',

`password` varchar(90) collate latin1_general_ci NOT NULL default '',

`contactemail` varchar(90) collate latin1_general_ci NOT NULL default '',

`date` int(15) NOT NULL default '0',

UNIQUE KEY `username` (`username`,`contactemail`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

 

Query

SELECT username

FROM msn

WHERE contactemail IN('test@test.com', 'test2@test.com')

 

EXPLAIN

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"

"1","SIMPLE","msn","index","","username","114","","373209","Using where; Using index"

[fenway]

Interesting... and if you switch it to just an index on the e-mail, do you then get index_merge? I'll have to check it on my box later on tonight.