Re: Warning: using a partial-field key prefix in search.
am 08.03.2004 13:43:21 von Sinisa Milivojevic
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
/ /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=gcdmb-bugs@m.gmane.org
Re: Warning: using a partial-field key prefix in search.
am 08.03.2004 19:20:01 von Heikki Tuuri
Hi!
I am able to repeat the warning with a relatively recent build of 4.1.2 with
just an empty table.
The 'hack' function below apparently copies some list of ranges from
QUICK_SELECT to QUICK_SELECT_DESC.
Then, in QUICK_SELECT_DESC::get_next(), 'range' is nonsensical. It is on a
4-byte integer column, but the key value in the range is only 2 bytes!
People have reported this warning in the .err log also before.
"
----- Original Message -----
From: ""Mechain Marc""
Newsgroups: mailing.database.mysql
Sent: Tuesday, August 19, 2003 5:09 PM
Subject: What's the meaning of: "InnoDB: Warning: using a partial-field key
prefix in search"
"
Unfortunately, Marc never told what MySQL version he was using.
Regards,
Heikki
/sql/opt_range.cc:
/*
This is a hack: we inherit from QUICK_SELECT so that we can use the
get_next() interface, but we have to hold a pointer to the original
QUICK_SELECT because its data are used all over the place. What
should be done is to factor out the data that is needed into a base
class (QUICK_SELECT), and then have two subclasses (_ASC and _DESC)
which handle the ranges and implement the get_next() function. But
for now, this seems to work right at least.
*/
QUICK_SELECT_DESC::QUICK_SELECT_DESC(QUICK_SELECT *q, uint used_key_parts)
: QUICK_SELECT(*q), rev_it(rev_ranges)
{
bool not_read_after_key = file->table_flags() & HA_NOT_READ_AFTER_KEY;
QUICK_RANGE *r;
it.rewind();
for (r = it++; r; r = it++)
{
rev_ranges.push_front(r);
if (not_read_after_key && range_reads_after_key(r))
{
it.rewind(); // Reset range
error = HA_ERR_UNSUPPORTED;
dont_free=1; // Don't free memory from
'q'
return;
}
}
/* Remove EQ_RANGE flag for keys that are not using the full key */
for (r = rev_it++; r; r = rev_it++)
{
if ((r->flag & EQ_RANGE) &&
head->key_info[index].key_length != r->max_length)
r->flag&= ~EQ_RANGE;
}
rev_it.rewind();
q->dont_free=1; // Don't free shared mem
delete q;
}
int QUICK_SELECT_DESC::get_next()
{
DBUG_ENTER("QUICK_SELECT_DESC::get_next");
/* The max key is handled as follows:
* - if there is NO_MAX_RANGE, start at the end and move backwards
* - if it is an EQ_RANGE, which means that max key covers the entire
* key, go directly to the key and read through it (sorting backwards
is
* same as sorting forwards)
* - if it is NEAR_MAX, go to the key or next, step back once, and
* move backwards
* - otherwise (not NEAR_MAX == include the key), go after the key,
* step back once, and move backwards
*/
for (;;)
{
int result;
if (range)
{ // Already read through key
result = ((range->flag & EQ_RANGE)
? file->index_next_same(record, (byte*) range->min_key,
range->min_length) :
file->index_prev(record));
if (!result)
{
if (cmp_prev(*rev_it.ref()) == 0)
DBUG_RETURN(0);
}
else if (result != HA_ERR_END_OF_FILE)
DBUG_RETURN(result);
}
if (!(range=rev_it++))
DBUG_RETURN(HA_ERR_END_OF_FILE); // All ranges used
if (range->flag & NO_MAX_RANGE) // Read last record
{
int local_error;
if ((local_error=file->index_last(record)))
DBUG_RETURN(local_error); // Empty table
if (cmp_prev(range) == 0)
DBUG_RETURN(0);
range=0; // No matching records; go to next range
continue;
}
if (range->flag & EQ_RANGE)
{
result = file->index_read(record, (byte*) range->max_key,
range->max_length, HA_READ_KEY_EXACT);
}
else
{
DBUG_ASSERT(range->flag & NEAR_MAX || range_reads_after_key(range));
#ifndef NOT_IMPLEMENTED_YET
result=file->index_read(record, (byte*) range->max_key,
range->max_length,
((range->flag & NEAR_MAX) ?
HA_READ_BEFORE_KEY :
HA_READ_PREFIX_LAST_OR_PREV\
));
#else
/*
Heikki changed Sept 11, 2002: since InnoDB does not store the cursor
position if READ_KEY_EXACT is used to a primary key with all
key columns specified, we must use below HA_READ_KEY_OR_NEXT,
so that InnoDB stores the cursor position and is able to move
the cursor one step backward after the search.
*/
/*
Note: even if max_key is only a prefix, HA_READ_AFTER_KEY will
do the right thing - go past all keys which match the prefix
*/
result=file->index_read(record, (byte*) range->max_key,
range->max_length,
((range->flag & NEAR_MAX) ?
HA_READ_KEY_OR_NEXT : HA_READ_AFTER_KEY));
result = file->index_prev(record);
#endi
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org