List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:July 9 2002 1:33pm
Subject:Re: Drop keys does not stop indexes being created
View as plain text  
You check index files, not table names.
myisamchk -k 0 -rq $MYSQLDIR/test/BestGuess.MYI



Archer Barrie wrote:

>>Description
>>
>Having followed the instructions in section 5.2.8 to create a table using
>LOAD
>DATA INFILE *without* its indexes, the indexes are *still* created at the
>end 
>of the LOAD DATA INFILE statement
>
>>How-To-Repeat:
>>
>The following shell script demonstrates the problem. There are two directory
>listings, before and after the load. In the second one BestGuess.MYI should
>be unchanged at 1024 bytes but it isn't because the indexes have been
>created even though myisamchk -k 0 has been executed
>
>Script assumes:
>/tmp is available
>databases are in /usr/local/var (line 1)
>
><<<start script>>>
>MYSQLDIR=/usr/local/var
>
>cat >/tmp/bgdata <<EOF
>1|1|'Fordham'|'Emma'|344|'12'|'94'|'7ka3hn5qhtDRbPkJ1uWX/A'|''|1|3|'Lexdon &
>Winstree'|'N'
>2|1|'Fordham'|'Enoch'|625|'22'|'318'|'Daf1Dt+XH3aq47D8gigWDw'|''|1|3|'Wakefi
>eld'|'N'
>3|1|'Fordham'|'George'|500|'12'|'164'|'i1L5pD8Q8gI+CFfqkkOWzQ'|''|1|3|'Saffr
>on Walden'|'N'
>4|1|'Fordham'|'Henry'|548|'14'|'144'|'Md3MtuwgcJtjGs5rKcU8pg'|''|1|3|'Saint
>Ives'|'N'
>5|1|'Fordham'|'Henry'|209|'3'|'43'|'EpyvpR6wK4tXDCg0dUWThA'|''|1|3|'Edmonton
>'|'N'
>6|1|'Fordham'|'John'|116|'14'|'7'|'SE1U6ewpSZkU0wA0WRe/pg'|''|1|3|'Cambridge
>'|'N'
>7|1|'Fordham'|'Lewis'|348|'14'|'68'|'JeHwC7Jnhx6VNFabWRCwQA'|''|1|3|'Linton'
>|'N'
>8|1|'Fordham'|'Stephen'|268|'6'|'359'|'LS/iLpTXxrfqwlfAVoJdyQ'|''|1|3|'Hatfi
>eld& Welwyn'|'N'
>9|1|'Fordham'|'female'|416|'14'|'89'|'s91dCImO+HeyIy6rXxldGA'|''|1|3|'Newmar
>ket'|'N'
>10|2|'FOTHERGILL'|'ROBERT'|206|'25'|'289'|'UIOL6Q5/8cMoeA/k/DmlZg'|''|1|3|'E
>astWard'|'N'
>EOF
>
>
>mysql -D test <<EOF
>drop table BestGuess;
>EOF
>
>mysql -D test <<EOF
>create table BestGuess
>  (
>  RecordNumber INT UNSIGNED NOT NULL AUTO_INCREMENT,
>  ChunkNumber INT UNSIGNED NOT NULL,
>  Confirmed VARCHAR( 1 ),
>  Surname VARCHAR( 50 ) NOT NULL,
>  GivenName VARCHAR( 50 ) NOT NULL,
>  AgeAtDeath VARCHAR(50),
>  DistrictNumber SMALLINT UNSIGNED NOT NULL,
>  District VARCHAR( 50 ) NOT NULL,
>  DistrictFlag TINYINT UNSIGNED NOT NULL,
>  Volume VARCHAR( 50 ) NOT NULL,
>  Page VARCHAR( 50 ) NOT NULL,
>  Hash CHAR(22) NOT NULL,
>  RecordTypeID TINYINT NOT NULL,
>  QuarterNumber SMALLINT NOT NULL,
>  PRIMARY KEY (RecordNumber),
>  INDEX (ChunkNumber),
>  INDEX (Surname(10),GivenName(10),QuarterNumber),
>  INDEX (Surname(10),GivenName(10),DistrictNumber),
>  INDEX (Surname(10),QuarterNumber),
>  INDEX (Surname(10),DistrictNumber),
>  INDEX (GivenName(10),QuarterNumber),
>  INDEX (Volume,Page,QuarterNumber),
>  INDEX (DistrictNumber,QuarterNumber)  
>  );
>EOF
>
>ls -l $MYSQLDIR/test/BestGuess.M*
>
>mysqladmin flush-tables
>myisamchk -k 0 -rq $MYSQLDIR/test/BestGuess
>
>mysql -D test <<EOF
>LOAD DATA INFILE '/tmp/bgdata'
>IGNORE
>INTO TABLE BestGuess
>FIELDS TERMINATED BY '|'
>ENCLOSED BY "'"
>(RecordNumber, ChunkNumber,Surname,GivenName,
>DistrictNumber,Volume,Page,Hash,
>AgeAtDeath,RecordTypeID,QuarterNumber,
>District,Confirmed)
>EOF
>
>ls -l $MYSQLDIR/test/BestGuess.M*
>
><<<end script>>>
>
>>Fix:
>>
>Unknown
>
>>Submitter-Id:	barrie
>>Originator:	Barrie Archer
>>Organization:  
>>
>FreeBMD (http://freebmd.rootsweb.com)
>
>>MySQL support: none
>>Synopsis:	drop keys does not inhibit creation of indexes on load
>>Severity:	serious
>>Priority:	medium
>>Category:	mysql
>>Class:		sw-bug
>>Release:	mysql-3.23.51 (Source distribution)
>>Server: /usr/local/bin/mysqladmin  Ver 8.23 Distrib 3.23.51, for
>>
>unknown-freebsdelf4.2 on i386
>Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
>This software comes with ABSOLUTELY NO WARRANTY. This is free software,
>and you are welcome to modify and redistribute it under the GPL license
>
>Server version		3.23.51
>Protocol version	10
>Connection		Localhost via UNIX socket
>UNIX socket		/tmp/mysql.sock
>Uptime:			5 days 6 hours 7 min 19 sec
>
>Threads: 2  Questions: 2215677  Slow queries: 22  Opens: 300  Flush tables:
>16  Open tables: 1 Queries per second avg: 4.880
>
>>Environment:
>>
>	
>System: FreeBSD sump.inv.thebunker.net 4.2-RELEASE FreeBSD 4.2-RELEASE #0:
>Wed Feb  7 18:04:50 GMT 2001
>root@stripped:/usr/src/sys/compile/SUMP  i386
>
>
>Some paths:  /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc
>/usr/bin/cc
>GCC: Using builtin specs.
>gcc version 2.95.2 19991024 (release)
>Compilation info: CC='gcc'  CFLAGS=''  CXX='g++'  CXXFLAGS=''  LDFLAGS=''
>LIBC: 
>-r--r--r--  1 root  wheel  1169076 Nov 20  2000 /usr/lib/libc.a
>lrwxrwxrwx  1 root  wheel  9 Feb  6  2001 /usr/lib/libc.so -> libc.so.4
>-r--r--r--  1 root  wheel  559196 Nov 20  2000 /usr/lib/libc.so.4
>Configure command: ./configure --with-innodb
>
>
>---------------------------------------------------------------------
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread113901@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-mysql=adooloo.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>---------------------------------------------------------------------
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread114083@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


Thread
Drop keys does not stop indexes being createdArcher Barrie9 Jul
  • Re: Drop keys does not stop indexes being createdGerald Clark9 Jul
    • choice of field types and performanceMarko Djukic9 Jul
      • Re: choice of field types and performanceRoger Baklund9 Jul
        • Binding Timestamp Columns with myODBCMateus Begossi9 Jul