Using char index to find numeric values

Question

I have a column on a mysql table that stores mostly numeric values, but sometimes strings. It's defined as VARCHAR(20). There is an index on this column for the first four characters.

ADD INDEX `refNumber` USING BTREE(`refNumber`(4));

Since the field is mostly numeric, it is useful for the user to be able to query for values that fall within an numeric range (e.g., 100 to 2000). If I use a numeric comparison, this index is not used.

WHERE refNumber BETWEEN 100 AND 2000

If I use a string comparison, I get some values I don't want (e.g., 10000 comes back when querying for a range of 100 to 2000).

WHERE refNumber BETWEEN '100' AND '2000'

Is there a good solution to this?

Note: there are some values that are recorded with zeros padded on the front like 0234, which should be returned when looking for values between 100 and 2000.

Answer 1

Three possibilities

1) Separate the numeric values into their own column.

2) If you MUST keep things as they are, decide on a maximum length for the numbers, zero- or blank-pad the numbers to that length.

3) I don't know if MySQL supports function-based indexes, but that might be an option. if so, write a function that returns the extracted numeric value and use that as the basis of the index.

Answer 2

You can try using the string comparison first, so the index is used, and still do the numeric comparison afterwards. It shouldn't slow things too much, since the second filter will only apply to a small subset of the rows.

WHERE refNumber BEETWEEN '100' AND '2000' AND CAST(refNumber as SIGNED INTEGER) BEETWEEN 100 AND 2000