0

I already have a product in production where i used Entity Framework with SQL Server as db. I've used full text search wherein i store binary data (filestream) along with the file extension which is used by the Full Text Search Engine to index. Not to mention that was a cake walk.

Now, am planning to move to mysql (cuz of obvious reasons -- cost, open-source etc). The product is in SAAS model (although this is the time i can actually take definite call as traffic isn't high) so volume will be high, so search engine should be scalable.

Migration to mysql is easy (will be using INNODB, again for obvious reasons), am only stuck with Full Text Search as right now only the binary data is stored in Sql Server. Although INNODB supports full text search in version 5.6 onwards I did not find exact way to Full Text Index (that is using binary).

Am not sure of using third party full text search engine (Lucene, Sphinx etc) as my searches will be combination of structured and unstructured, for ex: All the Customers from Ohio (structured data as i capture and store the information in RDB) and who have "insurance" in their set of documents (unstrucutred) uploaded. In SQL Server i use "ContainsTable" which gives me ranked results.

I have following questions :-

  1. Will this movement really be fruitful in the long run? Lets say i do migrate the existing data (in 4 figures).
  2. Although INNODB provides all the necessary features, does that match in SQL Server ease of use, administration and scale?

Some more questions...

  1. Can i move the full text table to mysql as is? I guess i cant index blob columns.
  2. Should i use mysql full text or any third party engine?
Wali
  • 440
  • 1
  • 4
  • 21

1 Answers1

1

  1. Thats really hard to say, ive used both system and i do prefeer MySql. Allthough i find that index rebuilding is alot faster in MSSQL. A tip, in MySQL, if you have alot of updates to the fulltext tables, that you simply drop the whole index, do the update and readd the FT index. This will save you insane amount of time and give you less headace.

  2. I make use of MySQL workbench. It has proven to me to be very very useful. But here, MSSQL has a massive advantage with Studio Manager with alot more feature and scalability.

The question itself is very broad there are so many aspects. If you biggest concern is cost than MySQL is the best option. Performance wise if the MySQL server setup is good i have seen very little difference in performace between the RDBS.

Im not sure if my question was helpful but i hope it gave you a little bit insight.

EDIT:

  1. No you cannot FT in blob.
  2. I suggest a document based search engine then like SOLR, Lucene...
Mad Dog Tannen
  • 6,725
  • 4
  • 28
  • 53
  • It did give lot of insight and gave me confidence that my move is not bad at least and worth trying... However, my major issue is movement of full text table to mysql which looks like a challenge at this point in time as am storing the data in binary. I guess mysql doesn't index binary (filestream) data just the way sql server does, am not too familiar with mysql fulltext search so not confident. Can you add more thoughts on it? – Wali Oct 02 '13 at 07:28
  • Well, i found this page on stackoverflow. http://stackoverflow.com/questions/17/binary-data-in-mysql , maybe you should look into using SOLR for instance, as storing binary data is not recommended in a relational database by some people. Maybe its possible to store the data using binary converter functions into a column of type TEXT (w fulltext index on it)? – Mad Dog Tannen Oct 02 '13 at 08:11
  • Point taken Kay! But is there any way i can move the binary file stream from Sql Server and achieve full text search out of it? – Wali Oct 03 '13 at 13:15
  • I found this in the documentation (http://dev.mysql.com/doc/refman/5.6/en/blob.html) "If you use the BINARY attribute with a TEXT data type, the column is assigned the binary collation of the column character set." Unfortunatley i cannot say for sure if you can move the binary stream from sql server into mysql table with these definition. Can you just give that little part a try on your side? Should not take to long to figure out right? – Mad Dog Tannen Oct 03 '13 at 13:44