List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:October 22 2005 11:50pm
Subject:Re: implicit cast forces table scan?
View as plain text  
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



Thread
implicit cast forces table scan?Olaf Faaland19 Oct
  • Re: implicit cast forces table scan?Jeff Smelser19 Oct
  • Re: implicit cast forces table scan?Michael Stassen21 Oct
  • Re: implicit cast forces table scan?Gleb Paharenko21 Oct
    • Re: implicit cast forces table scan?Michael Stassen21 Oct
      • Re: implicit cast forces table scan?Gleb Paharenko23 Oct
        • Re: implicit cast forces table scan?Michael Stassen24 Oct
RE: implicit cast forces table scan?Olaf Faaland21 Oct