MySQL Full Text Search instead of LIKE

There are lots of information in the internet which you can find about full text search syntax of MySQL. A good tutorial is of course in the MySQL’s Documentation.The other informations you find are coming from there generally.

But something is missing. How can I make regular expression search using full text search engine! As the rich web interface things and other WEB 2.0 called web sites are increasing the usage of javascript and JSON is also increasing. Most of AJAX requests are now returning JSON datas and they are formatted in the javascript.

Well as the usage of json is increased, I also thing that some people, like me, are started to keep data as json in their databases. Of course that I do not mean that instead of keeping database rows, keep json text files.

Well but as this is the thing, I’m a bit confused how to filter this data. How do you get a row with something like this in it’s json data: “foo”:”bar”.

Many will think that that’s possible like this:

SELECT * FROM foo WHERE bar LIKE ‘%”foo”:”bar”%’;

Of course that this is a solution, and it will return to you the data you want! But what about performance. You may increase this performance! This is not a linear performance increase though. When you will start to deal with then thousands of rows, the search will be slower and slower. At least this was what I was thinkinh.

Well I tried to do my own benchmark tests which you can think that they were pathetic, with my phpmyadmin and mysql 5 on my local server. My test data was a table with 2500+ plus rows and the results returning were 500+. And you know that, the execution times didn’t changed a lot. They were approximately the same. The field which I was looking had a fulltext index of course, maybe this had affected a lot. Maybe someone else will. This maybe because of the pattern I was looking for. As I was looking for json formatted data, instead of json formatting, if I had just searched ‘%bar%’ things would be different.

Any ideas?