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.
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.