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.

Bob
http://totallyfreeads.com

queries like:

work for me but take a little longer to run.

--Philip
Links 2.0 moderator

Hi,

What's the output for explain for the first select? I'm pretty sure the BINARY is throwing it off. Do you need case sensitivity? If you define your column in the create table as a varchar with binary as an attribute it'll be case sensitive, and I think would use the index.

Cheers,

Alex
--
Gossamer Threads Inc.

Thanks guys, to get it to optimize and use an index you need to set the columns to binary for case sensitivity so I found.

Bob
http://totallyfreeads.com

Hi Alex,

Are Links SQL columns defined as binaries i.e. able to accept case sensitive UPDATES and SELECTS?

Example:

Updating the Title column from the Links Table in LSQL:

->update({ Title => "eBAY" }, { Title => "ebay" } );


http://www.iuni.com/...tware/web/index.html
Links Plugins