Clever Geek Handbook

Search entire database table with multiple fields using php

I have a mysql database table with about 15 fields. I want to search the whole field for user input string. I don't want to use "or" in my query to combine multiple fields, as this will slow down processing. Can anyone help me with an alternative method?

+3


source to share


2 answers


You can create an FULLTEXT

index for any number of fields. The performance should be ok (much better than LIKE

).

Example:

ALTER TABLE table_name ADD FULLTEXT ft_index_name (col1, col2, col3);


Then for the request:

SELECT * FROM table_name WHERE MATCH(col1, col2, col3) AGAINST ('search_term')

If you need more performance, take a look at Sphinx , for example, which is a drop-in replacement for native MySQL FULLTEXT

.

+4


source


If you don't want to use OR, it's better to use concat

to complete all 15 fields and then search for the needle with like

. Something like that:

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 if this would be any better than using OR.

+3


source






More articles:


All Articles