List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:May 14 2012 2:43pm
Subject:Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this
View as plain text  
----- Original Message -----
> From: "Ananda Kumar" <anandkl@stripped>
> 
> If numeric, then why are u using quotes. With quotes, mysql will
> ignore the index and do a full table scan

Will it? Common sense dictates that it would convert to the column's native type before
comparing; and a quick explain seems to confirm this.

That being said, it *is* better to use the actual column type from the start, simply to
avoid the cost of implicit conversions.


mysql> desc user;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                 
     |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment        
     |
[...]
16 rows in set (0.04 sec)

mysql> explain select * from user where id = 1;
+----+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table             | type  | possible_keys | key     | key_len | ref  
| rows | Extra |
+----+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      |              user | const | PRIMARY       | PRIMARY | 4       | const
|    1 |       |
+----+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.07 sec)

mysql> explain select * from user where id = '1';
+----+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table             | type  | possible_keys | key     | key_len | ref  
| rows | Extra |
+----+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      |              user | const | PRIMARY       | PRIMARY | 4       | const
|    1 |       |
+----+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Thread
Mysql is toying me... why sometimes an insert or update can be slow!?I getting bald cuz thisAndrés Tello12 May
  • RE: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz thisSteven Staples14 May
  • Re: Mysql is toying me... why sometimes an insert or update can beslow!? I getting bald cuz thisAnanda Kumar14 May
    • Re: Mysql is toying me... why sometimes an insert or update can beslow!? I getting bald cuz thisAndrés Tello14 May
      • Re: Mysql is toying me... why sometimes an insert or update can beslow!? I getting bald cuz thisAnanda Kumar14 May
        • Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz thisJohan De Meersman14 May
          • Re: Mysql is toying me... why sometimes an insert or update can beslow!? I getting bald cuz thisAnanda Kumar14 May