Why dosn't this select statement use and index,
SELECT username,email FROM members WHERE BINARY email='$mail';
Explain tells me that this is not good.
table type possible_keys key key_len ref rows Extra
members ALL NULL NULL NULL NULL 5 where used
These are good -
SELECT username,email FROM members WHERE email='$mail';
SELECT email FROM members WHERE BINARY email='$mail';
These are not -
SELECT username,email FROM members WHERE BINARY email='$mail';
SELECT username FROM members WHERE BINARY email='$mail';
Username and email are seperately indexed and are both unique indexes.
I need this select to be case sensitive.
This select is case sensitive and is better, but still not good -
SELECT username,email FROM members WHERE email LIKE "$mail%";
From explain -
table type possible_keys key key_len ref rows Extra
members range email email 50 NULL 1 where used
Any one know why the first select won't optimise of how I can optimize it.
thanks
Bob
SELECT username,email FROM members WHERE BINARY email='$mail';
Explain tells me that this is not good.
table type possible_keys key key_len ref rows Extra
members ALL NULL NULL NULL NULL 5 where used
These are good -
SELECT username,email FROM members WHERE email='$mail';
SELECT email FROM members WHERE BINARY email='$mail';
These are not -
SELECT username,email FROM members WHERE BINARY email='$mail';
SELECT username FROM members WHERE BINARY email='$mail';
Username and email are seperately indexed and are both unique indexes.
I need this select to be case sensitive.
This select is case sensitive and is better, but still not good -
SELECT username,email FROM members WHERE email LIKE "$mail%";
From explain -
table type possible_keys key key_len ref rows Extra
members range email email 50 NULL 1 where used
Any one know why the first select won't optimise of how I can optimize it.
thanks
Bob