Indexing Weirdness in MySQL

[bubblenut]

Before I begin, note that this is a contrived example.

Scenario:
I have a table with two fields in it, one varchar field and one integer field. Both fields are indexed. The varchar field often contains just an integer.

Problem:
If I search for an integer in the varchar field and don't quote it MySQL refuses to use the index on that field. No such problem occurs the othe way, if I quote the integer when searching agains the integer field.

Example:

# field1 - varchar
# field2 - int

# Uses Index Fine
SELECT * FROM table WHERE field1 = '1234';
SELECT * FROM table WHERE field2 = 1234;
SELECT * FROM table WHERE field2 = '1234';

# Fails To Use Index
SELECT * FROM table WHERE field1 = 1234;

Any ideas why this is?

[mjax]

Even if the varchar field contains an integer, it still is a varchar. So MySQL has to compare each row and convert the varchar value to an integer to compare it to 1234. That's why a table scan is used and not the index.