Clever Geek Handbook

PHP and MySQL search across multiple columns

I need a way to search 50+ columns, both varchar and int, in a specific table. I've seen people recommend Lucene and Sphinx, but I don't know how to use that, and MySQL Full-Text won't let me concatenate more than 16 columns or both varchar and int columns.

+2


source to share


2 answers


If your table is not updating much, or some lag before the changes are reflected in the search is acceptable, perhaps you can create a periodically restored view that concatenates all your columns into LONGTEXT

and has a full-text index.



+2


source


You might want to consider expanding the table and chopping it off a bit. Take all your varchar columns and store the data in one table "Search_Strings"; do the same for int columns:

original table

id
always_field1
always_field2
varstring1
...
varstringx
int1
...
inty

new main_table (same number of rows as original table, but very few columns)

id
always_field1
always_field2


Search_Strings (

id (FK to main_table)
old_column_name
string_value

Search_Ints

id (FK to main_table)
old_column_name
int_value

With this setting, you only need the full text for string_value. Sorry, I cannot suggest anything with the Sphinx or Lucena as I am not familiar with them.

0


source






More articles:


All Articles