---------- Weitergeleitete Nachricht ----------
Subject: Re: Another Performance query Date: Mon, 22 Mar 2004 16:13:29 +0100 From: Stefan Kuhn <[EMAIL PROTECTED]> To: A Z <[EMAIL PROTECTED]> Indices can never be used with like "%x" (but with like "x%). This question won't use indeces, neither in myisam nor in innodb. If you think about the principles behind indices, you will see why indices can't work with like starting with a joker. Stefan Am Monday 22 March 2004 15:39 schrieb A Z: > here we go: > > explain select * from properties where reference like > '%2332' > +-----------------------------------------------------+ > Table � type � possible keys � key � key_len � ref � > rows � Extra > +-----------------------------------------------------+ > Properties � All � NULL � NULL � NULL � NULL � NULL � > 2923 � Using Where > > describe properties; > +-----------------------------------------------------+ > Field � Type � Null � Key � Default � Extra > +-----------------------------------------------------+ > Reference � VarChar(7) � � PRI � � > > show index from properties; > +-----------------------------------------------------+ > Table � Non_unique � Key_name � Seq_in_index � > Column_name � Collation � Cardinality � Sub_part � > Pakced � Null � Index_type � Comment > +-----------------------------------------------------+ > properties � 0 � PRIMARY � 1 � Reference � A � 2923 � > NULL � NULL � � BTREE > > regards > > > > --- Benoit St-Jean <[EMAIL PROTECTED]> wrote: > A > > Z wrote: > > >Thanks for your replies regarding to my previous > > >query. > > > > > >We have encountered another problem: > > >MySQL 4.0.14, INNODB. > > >A table does have an Index on Field1, this field > > >(Field1) is also the Primary Key. Querying on this > > >field takes a long time, running along with Explain > > >command it displays that it does not use the index. > > > > > >Prior converting to INNODB it had been working fine > > > > in > > > > >MYISAM. > > > > > >What could cause this problem? > > > > Can you provide us with the EXPLAIN of the query, a > > DESCRIBE TABLE and a > > SHOW INDEX to help us pinpoint what the problem is? > > ___________________________________________________________ > Yahoo! Messenger - Communicate instantly..."Ping" > your friends today! Download Messenger Now > http://uk.messenger.yahoo.com/download/index.html -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Z�lpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu ------------------------------------------------------- -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Z�lpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
