Impossible WHERE noticed?

[fry2010]

I have several tables, all with a primary key set. But I have just performed an explain on them, and it appears that the primary key is not being used, even though the field is being used in a where clause. I also notice that when I change the tables to include a compound key that it then uses the primary key. Here is one of the problematic tables:

 

create table authors (
username varchar(22) not null PRIMARY KEY,
email char(60) not null,
password char(133) not null,
website char(120) not null default '-',
account_type tinyint(1) not null default '0',
profile char(150) not null default '-'
);

 

When using:

 

EXPLAIN SELECT * FROM authors WHERE username = 'username'

 

I get this result:

 

possible_keys: NULL;

key: NULL;

ref: NULL;

rows: NULL;

extra: Impossible WHERE noticed after reading const tables;

 

This occurs on several tables. But when I change the table to this:

 

create table authors (
username varchar(22) not null,
email char(60) not null,
password char(133) not null,
website char(120) not null default '-',
account_type tinyint(1) not null default '0',
profile char(150) not null default '-',
PRIMARY KEY (username, email)
);

 

 

I get this result from the explain:

 

possible_keys: PRIMARY;

key: PRIMARY;

ref: const;

rows: 1;

extra: Using where, Using index;

 

 

Am I missing something?

 

[fry2010]

I forgot to say that it is mysql version 5.1

[fry2010]

Could this be to do with the fact I specified NOT NULL values, and since the query I enter does not match any record, it therefore cannot return any information about the query? Or something along those lines?

[fenway]

It's because the PK is indexed, and it knows nothing matches.

 

But NEVER use a varchar for a pK.

[fry2010]

great, thanks again fenway. I just used varchar in that to test the explain.

[mikosiko]

I forgot to say that it is mysql version 5.1

 

probable explanation:

http://bugs.mysql.com/bug.php?id=29087

 

there is a patch published.... also check if you have a record with "username" containing a blank space at the end.

 

fenway gave you a very good advice regarding varchars/PK's

[fry2010]

ok.

 

I have another question:

 

I have found somewhere else that says another optimization with a query is to use LIMIT 1, but if you are selecting from a primary key, would making it LIMIT 1 even make a difference since only 1 row is returned anyway?

 

eg:

 

"SELECT * FROM table WHERE primrykey = 'example' LIMIT 1";

 

as opposed to:

 

"SELECT * FROM table WHERE primrykey = 'example'";

[gizmola]

No there is no advantage to limiting a result set that only has one row in it.

[fry2010]

K thanks. Looks like my problems sorted for now.