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/
(----)
( >__< )
^^ ~~ ^^
