List:General Discussion« Previous MessageNext Message »
From:Thomas van Gulick Date:April 28 2007 5:16pm
Subject:Partial char key not used in conjuction with inequality comparison (MySQL5)
View as plain text  
Hello list!

I've noticed in MySQL 5.0 partial keys on character fields aren't always 
used. In 4.1 they were.
They seem not to be used when using inequality comparison.
I'm not sure whether this is a bug or intended (in the latter case I have to 
work around it to get the speed I got with 4.1 back into 5.0)

You can try for yourselves:

Setup:
CREATE TABLE t (T varchar(16) NOT NULL,KEY (T(1)));
INSERT INTO t SET T="test1";
INSERT INTO t SET T="test2";
INSERT INTO t SET T="test2";

Test:
EXPLAIN SELECT * FROM t WHERE T="x";
Result: key T used

EXPLAIN SELECT * FROM t WHERE T!="x";
Result: key T _unused_

Adding a key on entire field T works, but that's wasting a lot of space 
because I only want to test whether a certain field is empty or not.

Important note: MySQL4.1 *did* use key T in the second query!

Can anybody concur whether this happens for them too?
And if so, is this a bug?

grtz,
Thomas
-- 

Thread
Partial char key not used in conjuction with inequality comparison (MySQL5)Thomas van Gulick28 Apr
  • Re: Partial char key not used in conjuction with inequality comparison(MySQL5)Joerg Bruehe30 Apr