image you have application like this : 1 DB table, few int fields, few small varchar fields, and about 10 TEXT fields (contents variable - some data about 50 chars long, most about 100-200, some about 1000, very few more than 1000). Row count is in x0 000 - x00 000. Now, i need effective way to query like this (meta-language):
SELECT (1 if textfield1 LIKE %param1% ELSE 0) as r1,(1 if textfield2 LIKE %param2% ELSE 0) as r2, ... etc, for most of the text fields in 1 query typically (it is dynamic - may be 2 of them included, may be all of them).
Now the question - what is better for me, MySQL or MSSQL (probably express while possible,upgrade to full if really needed) ?
I know that MySQL have nice text indexes, which you have set on custom number of first characters, so i can balance it for the typical scenario (like this : http://fernandoipar.com/2009/08/12/indexing-text-columns-in-mysql/)
MSSQL has only full text indexing, which i have no experience with. Note that i do NOT need features like words proximity or similar words (run = ran; some stemming would be nice, but because data are multilingual it is impossible anyway). I need just common LIKE %word% system, thats all. And i also have to be able to find short substrings (2 chars).
Virtually the goal is to run as many as possible of these queries per hour/day (there wont be enough results, never ever, because they should be refreshed as often as possible), so think of this kind of efficiency as requirement :)
Thanx!
UPDATE: well aparently there is no way to use index for optimizing LIKE %foo% queries. So the new question is : is there any other way to speed up this type of queries ? (please omit things like "buy more ram or SSD" :)
