I’m working on a directory website for local businesses and services. We store different types of establishments like restaurants, hospitals, shopping centers etc in separate database tables since each category has unique fields beyond the basic name, location and contact info.
For search functionality, we created an index table that maps search terms to categories and tells us which table to query. We also built a master table combining names and addresses from all tables for cross-category searching.
Currently we’re using basic LIKE queries such as select * from master_table where business_name like "%searchterm%" but I know this approach has limitations.
I need advice on:
- How can I improve the overall architecture? Our current design feels inefficient
- Are there existing frameworks that can handle database searching with features like keyword splitting and relevance scoring?
- Would MySQL’s full-text search be sufficient for our needs?
I looked into tools like Elasticsearch and Solr but I’m not sure if they’re overkill. We need to search across our MySQL database tables, not crawl web pages. What would be the best approach for this scenario?
Nice setup! How many records are you dealing with across all your business tables? And what’s your expected search volume? These numbers matter a lot for deciding between fulltext search and something more robust like Elastic. What are your search requirements - do you need fuzzy matching for typos or just exact keyword matches?
Your architecture looks solid for a local business directory. Those LIKE queries are killing your performance since they can’t use indexes properly. I ran into the same issue building a service directory - MySQL’s full-text search was a game changer. No extra infrastructure needed, and you get relevance scoring plus way faster queries than pattern matching. MATCH AGAINST syntax will solve your speed problems. I’d stick with MySQL full-text for now. You can always move to Elasticsearch later if you need fancy stuff like geo-spatial search. The hassle of running a separate search cluster isn’t worth it unless you’re handling massive datasets or need real-time indexing across multiple sources.
mysql’s fulltext search could work fine here. i’ve used it on directory sites before - it handles keyword matching well enough. much simpler than spinning up elasticsearch just for business listings. try switching those like queries to match against before adding external tools.