LIKE is a string comparison. In order to do it, MySQL has to convert the
integer column into a string *before* it can test the string for a match.
MySQL is not clever enough to know that a string consisting entirely of
digits is compatible with an integer - as far as it is concerned "123%" is
the same as "mqa%". Furthermore, if you think if what you would have to do
if the table were on paper, you will see that it is not a trivial problem.
You are treating your data as a string. the fact that it is a string
consisting entirely of digits is irrelevant. You should therefore store it
as a string. If you don't need to index it as an integer, MySQL can convert
it to an integer on retrieval (try "colname+0"). If you need to have it
indexed both as an integer and as a string, the only thing I can think is
for you to break normalisation and store it twice - with consequent effects
on apps which insert/update records.
Alec
-----------------------------------------------
Hello
I have a table with an intger column called ID. I have
an index on it.
Now I want to get a result with all the rows whos ID
values start with lets say 12....i.e. I want all the
IDs with data -
12
123
1234
..........
I am using this query....
select * from tablename where id like '123%'
It is returning me correct results...but the query
does not seem to be using the INDEX?
My question is ... does MySQL not uses INDEX when we
execute a LIKE command on a NUMERIC data....
Is there any better solution to this query?
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
