SQL LIKE question

Question

I was wondering if there's a drawback (other than bad practice) to using something like this

SELECT * FROM my_table WHERE id LIKE '1';

where id is an integer. I know you're supposed to use id=1 but I am writing a java program and if everything can use LIKE it'll be a lot easier for me. Also, so far, everything works fine; I get the correct query results, so if there is no drawback I will continue doing it like this.

edit: I am using MySQL.

Answer 1

You'd need to look at the Query Execution Plan on your RDBMS to verify that LIKE with no wildcards is treated as efficiently as an = would be. A quick test in SQL Server shows that it would give you an index scan rather than a seek so I guess it doesn't look at that when generating the plan and for SQL Server using = would be much more efficient. I don't have a MySQL install to test against.

Edit: Just to update this SQL Server seems to handle it fine and do a seek when the data type is varchar. When it is run against an int column though you get the scan. This is because it does an implicit conversion to varchar on the int column so can't use the index.

Answer 2

Speed. [15-char filler as there's not much more to say]

Answer 3

Without using any wildcards with LIKE, is should be fine for your needs if the speed/efficiency is something you don't bother with.

Answer 4

I am writing a java program and if everything can use LIKE it'll be a lot easier for me

I don't understand it.
There is no conditional statement in Java or what?

Answer 5

MySQL will allow it, but will ignore the index:

mysql> describe METADATA_44;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| AtextId | int(11)      | NO   | PRI | NULL    |       | 
| num     | varchar(128) | YES  |     | NULL    |       | 
| title   | varchar(128) | YES  |     | NULL    |       | 
| file    | varchar(128) | YES  |     | NULL    |       | 
| context | varchar(128) | YES  |     | NULL    |       | 
| source  | varchar(128) | YES  |     | NULL    |       | 
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> explain select * from METADATA_44 where Atextid like '7';
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | METADATA_44 | ALL  | PRIMARY       | NULL | NULL    | NULL |  591 | Using where | 
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+

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

Answer 6

You are better off writing your query as

SELECT * FROM my_table WHERE id = 1;

otherwise mysql will have to typecast '1' to int which is the type of the column id so obviously there is a small performance penalty, when u know the type of the column supply the value according to that type