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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
