Re: Index bug when using MyIsam table and multiple columns index

[Thimble Smith]

On Thu, Apr 06, 2000 at 10:30:12PM -0600, Daniel Haglund wrote:
>>Description:
>	When I create a table using the MyIsam table type and set the
>	primary key to a text column and a date column and then try
>	to do a select using the index I always get a NULL return value.

Daniel, thanks for the excellent bug report.  In the future, good
solid bug reports like this should go to the bugs@stripped
list, so that they will get the fullest attention.

In this case, the bug isn't exactly what you described.  If you
change the Ticker table to be a char(8) instead of tinytext, your
queries will work.

  mysql> ALTER TABLE HistoricalData MODIFY Ticker CHAR(8) NOT NULL;

Please do that anyway, since tinytext is a variable-length type and
there's no need for it in your table (and it will slow you down
considerably)!  TINYTEXT is not tiny - it's basically just like a
VARCHAR, except it is handled differently underneath.  In general
it's not too useful (I personally don't know why one would use it,
but there's probably a good reason for it to exist).

This does appear to be a bug in how text/blob fields are working in
indexes.  We will fix this as soon as possible; by the next release
(or document in the manual why it is broken, if the fix is too hard
to get done quickly).

Thanks again,

Tim

>>How-To-Repeat:
>	Create a table:
>	CREATE TABLE HistoricalData (
>	Ticker tinytext NOT NULL,
>	Open float(5,3),
>	Close float(5,3),
>	High float(5,3),
>	Low float(5,3),
>	Volume int(4),
>	Date date NOT NULL,
>	PRIMARY KEY (Ticker(8),Date));
>
>Enter a little data into the table:
>INSERT INTO HistoricalData VALUES
> ('AIR',4.964,4.771,4.964,4.706,246000,'1990-12-17');
>INSERT INTO HistoricalData VALUES
> ('ABCC',1.938,2.031,2.031,1.938,18800,'1997-02-18');
>INSERT INTO HistoricalData VALUES
> ('ABM',20.688,20.438,20.688,20.375,182200,'2000-02-02');
>INSERT INTO HistoricalData VALUES
> ('AIR',5.157,4.964,5.222,4.964,520400,'1990-12-14');
>INSERT INTO HistoricalData VALUES
> ('ABCC',2.031,2.031,2.031,1.875,52700,'1997-02-14');
>INSERT INTO HistoricalData VALUES
> ('ABM',20.625,20.625,20.813,20.250,238700,'2000-02-01');
>INSERT INTO HistoricalData VALUES ('AIR',5.544,5.286,5.544,5.029,677900,'1990-12-13');
>        
>
>Then do a select to fetch the maximum date for a stock:
>SELECT MAX(Date) FROM HistoricalData WHERE Ticker='AIR';
>
>This will return NULL instead of 1990-12-17! It will run ok as long as Ticker and Date
> don't
>make up the index together but this is, as far as I know, the only way to get good
> performance
>for a query like this. This table will hold >15M records so speed is very
> important. Creating two separate indexes will work but not with the same performance. None
> or one index will also work.
>
>>Fix:
>I don't have a fix for this. If anyone does, please let me know quick. Thanks.
>
>>Originator:	Daniel Haglund dhaglund@stripped
>>Organization:	University of Colorado
>>MySQL support: none
>>Synopsis:	Multiple column index bug
>>Severity:	serious
>>Category:	mysql
>>Class:		sw-bug
>>Release:	mysql-3.23.13a-alpha (Source distribution)
>>Server: /usr/local/bin/mysqladmin  Ver 8.2 Distrib 3.23.13a-alpha, for
> pc-linux-gnu on i586
>TCX Datakonsult AB, by Monty
>
>Server version		3.23.13a-alpha
>Protocol version	10
>Connection		Localhost via UNIX socket
>UNIX socket		/tmp/mysql.sock
>Uptime:			7 hours 42 sec
>
>Threads: 2  Questions: 40709  Slow queries: 3  Opens: 23  Flush tables: 1  Open
> tables: 10
>>Environment:
>	<machine, os, target, libraries (multiple lines)>
>System: Linux spopps.uccs.edu 2.2.12-20 #3 Sun Mar 19 21:54:57 MST 2000 i586 Pentium
> 133
>Architecture: i586
>
>Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
>GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
>gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
>Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
>LIBC: 
>lrwxrwxrwx   1 root     root           13 Mar 19 12:48 /lib/libc.so.6 ->
> libc-2.1.2.so
>-rwxr-xr-x   1 root     root      4118299 Sep 20  1999 /lib/libc-2.1.2.so
>-rw-r--r--   1 root     root     20020054 Sep 20  1999 /usr/lib/libc.a
>-rw-r--r--   1 root     root          178 Sep 20  1999 /usr/lib/libc.so
>Configure command: ./configure  --prefix=/usr/local/mysql
>Perl: This is perl, version 5.005_03 built for i386-linu
>
>-- 
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread33497@stripped
>
>To unsubscribe, send a message to:
>    <mysql-unsubscribe-tim=mysql.com@stripped>
>

-- 
Tim Smith   < tim@stripped >  :MySQL Development Team:  Boone, NC  USA.