Re: Confusion following crossover from PGSQL

[Vince Gatto]

The wildcard for LIKE is %, not *, so your query should be this:

select * from detail where oid like '00000001%';


While I don't know this for sure, it could be that when you use a full text index on this data, MySQL is treating periods as word delimiters, and breaking your IP address up into four words. The default minimum word size for a full text index is four characters, so its likely only the last part (000/24) is making it into the index, and then only if the / character is not also a word delimiter.

Since IP addresses and ranges are clearly numeric data, you may want to consider using a numeric data type instead of varchar. And you clearly don't want to be using a full text index here. This isn't MySQL specific, the same goes for PGSQL.