CODEWITHSUNDEEP
phpmysql
Neeraj Dangol
Neeraj Dangol

Reputation: 223

Search an entire database table with multiple fields using php

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?

Upvotes: 3

Views: 1218

Answers (2)

julien_c
julien_c

Reputation: 5092

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.

Upvotes: 4

anubhava
anubhava

Reputation: 784898

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.

Upvotes: 3

Related Questions