List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:October 21 2005 7:56pm
Subject:Re: implicit cast forces table scan?
View as plain text  
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
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