-1

I am implementing a search function for a REST API and it is my first time dealing with large database searches.

My MySQL Location table has the following columns

| id | name | email | phone | postcode | address | verified |

A user can search for a Location by name, email, phone, postcode or address.

The input search string does not separate the different fields, i.e. there is only one input textbox and that string gets matched with each of the searchable fields above (this is purposely wanted).

What i have been doing up until now is taking the input string and running a MATCH ... AGAINST query for each searchable column, merging the results and returning.

However, i wouldn't it be better to create a support indexed table (location_query) that has two columns.

| location_id | match_query |

and when a Location is added i can add a new row to the location_query that has a value like

0 | {NAME} {EMAIL} {PHONE} {POSTCODE} {ADDRESS}

and then simply run a FULLTEXT search on the match_query column?

Could this work?

jarlh
  • 42,561
  • 8
  • 45
  • 63
WiserTheBassist
  • 49
  • 1
  • 6

1 Answers1

0

You haven't provided any details about your performance requirements, data amount and the current user load, but I'd suggested to use search engine (for example, Apache Solr) instead of RDBMS. This allows to fine-grain control of indexing and search strategies along with better performance.

S. Stas
  • 800
  • 4
  • 8
  • Hi thanks for your answer. I do not have any specific performance requirements at the moment and there is just mocking data for now, this is not a professional application but a learning project which means that also the user load is none. I am mainly trying to aim for scalability. I did not know about database search engines and will start to look into them. Thanks alot – WiserTheBassist Mar 21 '17 at 10:53
  • @WiserTheBassist, you may start with Apache Solr, it is stable and very friendly for beginners with descriptive documentation and community. – S. Stas Mar 21 '17 at 10:55
  • Yes, i have been looking into it, however i am on a Shared Hosting plan and limited to cPanel so i don't believe that i can install Apache Solr or any other Database search engine. – WiserTheBassist Mar 21 '17 at 11:14