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
--

Hi Thomas, all!


Thomas van Gulick wrote:
> [[...]]
>
> Test:
> EXPLAIN SELECT * FROM t WHERE T="x";
> Result: key T used
>
> EXPLAIN SELECT * FROM t WHERE T!="x";
> Result: key T _unused_

To be expected:
An "unequal" condition will evaluate to "true" for a very large
proportion of the index entries, so the selectivity will be too low.

The mere existence of an index does not imply that using it to access
the data is a good decision, doing this only pays off if the extra
accesses (to the index) are less work than is saved by not accessing
those data rows which are not returned by the index.

You wouldn't use a catalogue in manual search if a sequential scan would
return 1 out of 4 (or more) items, right ? The same holds here.


Joerg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
Office: (+49 30) 417 01 487 VoIP: 4464@sip.mysql.com