Dear, Michael!
> Did you read my earlier reply? This is neither an optimizer nor > > > version issue. I've sent my e-mail before I've read your. The problem is that I was testing my guessing on the table with a bit different structure than your (mine didn't have a primary key field 'id'). And optimizer has shown that it uses indexes! There is a verified bug: http://bugs.mysql.com/?id=14220 Thank you for good explanations! Michael Stassen wrote: > Gleb Paharenko wrote: >> Hello. >> >> I suggest you to check you query with MySQL 4.1.14 which might has a bit >> clever optimizer. > > Did you read my earlier reply? This is neither an optimizer nor version > issue. Consider: > > mysql> SELECT VERSION(); > +-----------+ > | VERSION() | > +-----------+ > | 4.1.15 | > +-----------+ > 1 row in set (0.01 sec) > > CREATE TABLE ict (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, > vcf VARCHAR(30), > INDEX (vcf) > ); > > INSERT INTO ict (vcf) > VALUES ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'), > > ('11'),('12'),('13'),('14'),('15'),('16'),('17'),('18'),('19'),('20'); > > EXPLAIN SELECT * FROM ict WHERE vcf = '17'; > +----+-------------+-------+------+--------+-----+-------+------+-------------+ > > | id | select_type | table | type | p_keys | key | ref | rows | > Extra | > +----+-------------+-------+------+--------+-----+-------+------+-------------+ > > | 1 | SIMPLE | ict | ref | vcf | vcf | const | 1 | Using > where | > +----+-------------+-------+------+--------+-----+-------+------+-------------+ > > 1 row in set (0.01 sec) > > EXPLAIN SELECT * FROM ict WHERE vcf = 17; > > +----+-------------+-------+------+--------+------+------+------+-------------+ > > | id | select_type | table | type | p_keys | key | ref | rows | > Extra | > +----+-------------+-------+------+--------+------+------+------+-------------+ > > | 1 | SIMPLE | ict | ALL | vcf | NULL | NULL | 20 | Using > where | > +----+-------------+-------+------+--------+------+------+------+-------------+ > > 1 row in set (0.01 sec) > > Why? Because there are many strings which evaluate to a given number. > For example: > > INSERT INTO ict (vcf) > VALUES (' 17'), ('+17'), ('17.0'), ('17,34'), ('17 dogs'); > > SELECT * FROM ict WHERE vcf = '17'; > +----+------+ > | id | vcf | > +----+------+ > | 17 | 17 | > +----+------+ > 1 row in set (0.00 sec) > > SELECT * FROM ict WHERE vcf = 17; > +----+---------+ > | id | vcf | > +----+---------+ > | 17 | 17 | > | 21 | 17 | > | 22 | +17 | > | 23 | 17.0 | > | 24 | 17,34 | > | 25 | 17 dogs | > +----+---------+ > 6 rows in set (0.00 sec) > > You see? The two WHERE clauses are actually different, so the optimizer > must treat them differently. No amount of optimizer cleverness can > change that. The moral of the story: Use numeric types to store > numbers, not string types. > > Michael > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
