Re: Warning: using a partial-field key prefix in search.

Kevin Day writes:
> 
> 
> I've got a query that worked in 3.23 but doesn't in 4.1.1:
> 
> 
> mysql> CREATE TABLE `h` (
>      ->   `p` int(11) unsigned NOT NULL auto_increment,
>      ->   `c` smallint(11) unsigned NOT NULL default '0',
>      ->   `s` smallint(11) NOT NULL default '0',
>      ->   PRIMARY KEY  (`p`),
>      ->   KEY `c` (`c`),
>      ->   KEY `s` (`s`),
>      ->   KEY `cs` (`c`,`s`)
>      -> ) TYPE=InnoDB DEFAULT CHARSET=latin1
>      -> ;
> Query OK, 0 rows affected (0.05 sec)
> 
> mysql> SELECT * FROM h use index (cs) WHERE c=1941 AND s>=0 ORDER BY p 
> DESC LIMIT 0,13
>      -> ;
> 040307  6:32:39  InnoDB: Warning: using a partial-field key prefix in 
> search.
> InnoDB: Table name test/h, index name PRIMARY. Last data field length 4 
> bytes,
> InnoDB: key ptr now exceeds key end by 2 bytes.
> InnoDB: Key value in the MySQL format:
>   len 2; hex 9507; asc ;
> Empty set (0.00 sec)
> 
> 
> It returns an empty set no matter what data should have been returned. 
> Removing the "use index" seems to fix it. It seems intermittent though, 
> sometimes it DOES work and I can't find any pattern.
> 
> Anyone bumped into this before?
> 

Hi!

Thank you for writting to us.

I have tested your problem with 4.1.2 and it works just fine.

This is my test script:

drop table if exists t1;
CREATE TABLE t1 ( `p` int(11) unsigned NOT NULL auto_increment,  `c` smallint(11) unsigned NOT NULL default '0',  `s` smallint(11) NOT NULL default '0',  PRIMARY KEY  (`p`),  KEY `c` (`c`),  KEY `s` (`s`),  KEY `cs` (`c`,`s`) ) TYPE=InnoDB DEFAULT CHARSET=latin1;
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
insert into t1 values (NULL,1941,1);
SELECT * FROM t1 use index (cs) WHERE c=1941 AND s>=0 ORDER BY p DESC LIMIT 0,13;
drop table if exists t1;

These are results:
p       c       s
41      1941    1
40      1941    1
39      1941    1
38      1941    1
37      1941    1
36      1941    1
35      1941    1
34      1941    1
33      1941    1
32      1941    1
31      1941    1
30      1941    1
29      1941    1


4.1.2 will be available in the couple of weeks.


-- 

Sincerely,

-- 
For technical support contracts, go to https://order.mysql.com/?ref=msmi
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Sinisa Milivojevic <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB
/_/  /_/\_, /___/\___\_\___/   Full time Developer and Support Coordinator
       <___/   www.mysql.com   Larnaca, Cyprus

Meet the MySQL at User Conference ! (April 14-16, 2004)
http://www.mysql.com/uc2004/


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]