Which indexing is better for simple checking

Question

I have a customer table having customer_id, customer_name, email,password,status etc fields.

Currently only customer_id is indexed (as it is the primary key)

I have few queries that select customers as follows

select * from customer 
where status=1 and email<>'' 
and email is not null 
and password<>'' 
and password is not null

This runs slow as I have 1.3 million records in it So I was thinking of adding index on email field. I want to know which indexing will make it better the simple index will work or I have to use FULLTEXT index

Erm... I'm sorry, but why don't you just try it? For the start, it's not known how many email-less and password-less records are in your DB right now. — raina77ow
I'd imagine a FULLTEXT index will be a waste - you're not searching the field in the query. But of course I don't know if none of your queries search the field. — Bernhard Barker
How many of your 1.3 million rows are returned ? If it returns most of them, indexes might not help you at all. — nos
Thanks for all the comments. all the comments above helped me in my work. — Imdad

RandomSeed RandomSeed · Accepted Answer · 2013-03-04T13:37:08

A FULLTEXT index is helpful for searching for words within a column.

If you really just search for empty (and non-null) emails and passwords, then a simple index will suffice.

For this very query, a more relevant index would be:

ALTER TABLE customer ADD INDEX (status, email, password);

[edit]

As correctly pointed out by Dukeling et al., such an index is probably useless if most of your customers do have an e-mail or a password set.

Assuming the above (most of your customers do have an e-mail and a password set), then your query returns many records, and any index will be of little help (as advised by nos and raina77ow).

The only thing one can be sure of, is that a FULLTEXT index is useless in this case.

Which indexing is better for simple checking

1 Answers