---------- 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]
