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
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
