I have a mysql database table with about 15 fields. I want to search the entire field for the string input by the user.. I dont want to use "or" in my query for joining multiple fields as it will slow down the processing. Can anyone please help me with alternate method?
1
-
you use this php class : code.google.com/p/anywhereindbmgraph– mgraph2012-03-22 10:00:27 +00:00Commented Mar 22, 2012 at 10:00
Add a comment
|
2 Answers
You can create a FULLTEXT index on any number of fields. Performance should be ok (much better than LIKE).
Example:
ALTER TABLE table_name ADD FULLTEXT ft_index_name (col1, col2, col3);
Then to query:
SELECT * FROM table_name WHERE MATCH(col1, col2, col3) AGAINST ('search_term')
If you need extra performance, look into Sphinx for example, which functions as a drop-in replacement for the native MySQL FULLTEXT.
Comments
If you don't want to use OR then better is to use concat to concat all 15 fields and then search for needle with like. Something like this:
select * from MyTable
where concat(col1,col2,col3,col4,col5,col6,col7,col8,col9,col11,col11,col12,col13,col14,col15)
like '%myval%';
However I'm not sure performance wise this will be any better than using OR.
Comments
