List:General Discussion« Previous MessageNext Message »
From:Randolph Chung Date:April 16 1999 11:57pm
Subject:BUG REPORT: Index usage anomalies
View as plain text  
Symptoms: MySQL uses the index for some range queries and not others, even
on the same field. It does not seem to do this consistently.

Version info:
--------------
mysql/bin/mysql  Ver 9.31 Distrib 3.22.20a, for sun-solaris2.6 (sparc)

Connection id:          1
Current database:       foo
Current user:           root@localhost
Server version          3.22.20a
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 1 min 33 sec

Threads: 1  Questions: 9  Slow queries: 0  Opens: 8  Flush tables: 1  Open
tables: 4

similar problems have been observed in a newer version of mysql under linux
(3.22.21)

OS info:
(solaris) SunOS twinkie.cs.cornell.edu 5.6 Generic_105181-12 sun4m sparc
(linux) Linux tausq.org 2.2.2 #1 Sun Feb 28 01:43:44 EST 1999 i686 unknown

Memory:
(solaris) 256M, quad-processor
(linux) 64M, PII-400

Distribution:
(solaris) binary, from web site
(linux) source, built with egcs

Table info:
twinkie[randolph]~/projects> mysql/bin/mysqldump -u root -p --no-data foo bar
Enter password: 
# MySQL dump 5.13
#
# Host: localhost    Database: foo
#--------------------------------------------------------
# Server version        3.22.20a

#
# Table structure for table 'bar'
#
CREATE TABLE bar (
  id int(11) DEFAULT '0' NOT NULL auto_increment,
  start_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  end_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY (id),
  KEY start_date (start_date),
  KEY end_date (end_date)
);

=======================
Problems:

mysql> explain select * from bar where start_date = 19900101;
+-------+------+---------------+------------+---------+------+------+-------+
| table | type | possible_keys | key        | key_len | ref  | rows | Extra |
+-------+------+---------------+------------+---------+------+------+-------+
| bar   | ref  | start_date    | start_date |       8 | ???  |    1 |       |
+-------+------+---------------+------------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from bar where start_date > 19900101;
+-------+-------+---------------+------------+---------+------+------+-------+
| table | type  | possible_keys | key        | key_len | ref  | rows | Extra |
+-------+-------+---------------+------------+---------+------+------+-------+
| bar   | range | start_date    | start_date |    NULL | NULL | 1045 |       |
+-------+-------+---------------+------------+---------+------+------+-------+
1 row in set (0.01 sec)

mysql> explain select * from bar where start_date < 19900101;
+-------+------+---------------+------+---------+------+-------+------------+
| table | type | possible_keys | key  | key_len | ref  | rows  | Extra      |
+-------+------+---------------+------+---------+------+-------+------------+
| bar   | ALL  | start_date    | NULL |    NULL | NULL | 10865 | where used |
+-------+------+---------------+------+---------+------+-------+------------+
1 row in set (0.02 sec)

Notice that the first two queries uses the index, but the last one doesn't.
(this is on the solaris machine)

On the linux machine, it uses the index for the first and third queries, but
not the second. The tables are set up similarly.

Looking in the documentation, there's no reference on why this may be so. I
have tried running isamchk -a on the tables with no obvious differences. The
data is generated randomly by a perl script:

#!/usr/bin/perl

use DBI;
$count = 10000;

$dsn = "DBI:mysql:database=foo;host=127.0.0.1";
$dbh = DBI->connect($dsn, "user", "password") || die $dbh->errstr;

foreach (1..$count) {
  $date1 = sprintf("%04d-%02d-%02d", int(rand(1)*100)+1900, int(rand(1)*12)+1,
                   int(rand(1)*28)+1);
  $date2 = sprintf("%04d-%02d-%02d", int(rand(1)*100)+1900, int(rand(1)*12)+1,
                   int(rand(1)*28)+1);

  $dbh->do("INSERT INTO bar (start_date, end_date) VALUES('$date1', '$date2')\n");
}

$dbh->disconnect;

Any pointers on how to get the index to work on all queries is much
appreciated. Please send replies to me personally. I will summarize responses.

randolph
rc42@stripped / tausq@stripped
-- 
   @..@                                         http://www.TauSq.org/
  (----)
 ( >__< )
 ^^ ~~ ^^

Thread
BUG REPORT: Index usage anomaliesRandolph Chung17 Apr
  • BUG REPORT: Index usage anomaliesMichael Widenius17 Apr
    • Re: BUG REPORT: Index usage anomaliesRandolph Chung17 Apr
      • Re: BUG REPORT: Index usage anomaliesMichael Widenius18 Apr