Re: Another Performance query
A Z wrote: here we go: explain select * from properties where reference like '%2332' The above query cannot use the index on reference in either MyISAM or InnoDB case. However, with MyISAM it will hurt less because you are scanning a plain data file instead of a B-tree. Suggestion for a workaround - add a column rev_reference with a key on it, update t1 set rev_reference = reverse(reference), and then do where rev_reference like '2332%'; -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Re: Another Performance query
-- 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]
Re: Another Performance query
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another Performance query
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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another Performance query
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? regards ___ Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
