Function-Based Index on numeric Column

I have a table containing some numeric columns, and i have to keep them numeric because most of the time i will benefit from that. But i need also to make a generic search on those columns using partial matches so in my where statement i will have something like

...where num_col1 like "1234%...

My question is:

Can i make a function based index on num_col1 casting the column开发者_如何学编程 to CHAR? I tried but it seems it is not possible.

If no, do you guys have any other suggestions on how can i speed up the results of my queries?

I read around that some possible solutions could be either to create a View of my original table and there change the column type to varchar and index that column or another solution could be to add an extra varchar column to my table and index that. I would like to avoid both of these solutions because i have a really big table containing already a huge amount of rows and huge amount of columns.

Thanks in advance to everybody, Best, N.

MySQL doesn't have function based indexes, virtual columns or indexed views (afaik).

Quoting the note on function based indexes in MySQL from my SQL Indexing Tutorial:

The backup solution is to create a real column in the table that holds the result of the expression. The column must be maintained by a trigger or by the application layer—whatever is more appropriate. The new column can be indexed like any other, SQL statements must query the new column (without the expression).

However, in your particular case, there is probably the risk that you will filter by an anywhere LIKE expression '%1234%'. In that case, b-tree will not help (explained in LIKE performance tuning/indexing). That would require full-text indexing, which works with MyISAM only.

I think your instinct to use the view is correct, to keep from duplicating the data. But what are the needs that are optimized by numbers in one case and a string search in another? Depending on those, it might make sense to take a look at the numbering system to see if you could search on a number range ( x >= 12340 and x < 12350 ) which would be much faster than a string compare.