在后端为 MYSQL 的网站上集成搜索 [英] Integrating search on a website where the backend is MYSQL
问题描述
我有一个城市位置搜索网站，我们开始收集所有可能类别的数据城市，如学校、学院、百货公司等，并将其信息存储在单独的表中，因为每个条目除了姓名、地址和电话号码外，还有不同的详细信息.
I have a location search website for a city, we started out with collecting data for all possible categories in the city like Schools, Colleges, Departmental Stores etc and stored their information in a separate table, as each entry had different details apart from their name, address and phone number.
我们不得不在网站中集成搜索功能，让人们能够找到信息，因此我们建立了一个索引表，其中存储了同一类别的类别和相关关键字以及如果搜索该类别则可以获取的表格.后来我们添加了搜索姓名和地址的功能，并将包含所有表中这些字段的另一个主表添加到一个位置.现在我的疑问是以下
We had to integrate search in the website to enable people to find information, so we built an index table where in we stored the categories and related keywords for the same category and the table which much be fetched if that category was searched for. Later on we added the functionality of searching on the name and address as well by adding another master table containing those fields from all the tables to one place. Now my doubt is the following
- 应用程序设计不当，我们写了像 select * from master where name like "%$input%" 这样的查询，所有的，因为我们的数据库是服务器端的 MYSQL 和 PHP，有什么建议我改进关于系统的设计?
- 人们想要更多功能，例如拆分关键字并根据相关性对它们进行排名等，是否有任何现成的框架可以在数据库上运行搜索.
- 我尝试在 MYSQL 中使用全文搜索，对我来说似乎很有效，这样就足够了吗?
如果我错了，请纠正我，我查看了 Lucene 和 Google 自定义搜索，他们不是通过抓取现有网页并构建自己的索引来制作索引吗?我在 mysql 数据库上有一组表，我必须在其中应用搜索.我有哪些选择?
Correct me if i am wrong, i had a look into Lucene and Google Custom Search, don't they work on making an index by crawling existing webpages and building their own index? I have a collection of tables on a mysql database on which i have to apply searching. What options do i have?
推荐答案
解决您的问题:
使用 %input% 非常糟糕.这将导致每次查询都进行全表扫描.在任何负载量下，甚至在远程大型数据集上，您的数据库服务器都会阻塞.
Using %input% is very bad. That will cause a full table scan every query. Under any amount of load or on even a remotely large dataset your DB server will choke.
单独使用 RDBMS 不是解决此问题的好方法.通过寻找单独的搜索解决方案，您正在寻找正确的地方.可以与您的 RDBMS 进行良好通信的东西是好的；在 RDBMS 中运行的东西不会满足您的需求.
An RDBMS alone is not a good solution for this. You are looking in the right place by seeking a separate solution for search. Something which can communicate well with your RDBMS is good; something that runs inside an RDBMS won't do what you need.
MySQL 中的全文搜索适用于非常基本的关键字搜索，仅此而已.有用的范围极其有限 - 您需要一个高度可预测的使用模型来利用内置搜索.它被称为搜索"，但实际上并不是大多数人认为的搜索方式.与我们期望从 Google 和 Bing 获得的搜索结果质量相比，它没有可比性.在搜索"这个词的意义上，它是别的东西——比如记事本与 Word.它们都需要输入，但仅此而已.
Full Text Search in MySQL is workable for very basic keyword searches, nothing more. The scope of usefulness is extremely limited - you need a highly predictable usage model to leverage the built-in searching. It is called "search" but it's not really search the way most people think of it. Compared to the quality of search results we have come to expect from Google and Bing, it does not compare. In that sense of the word "search", it is something else - like Notepad vs Word. They both are things to type in, but that's about it.
就处理搜索的单独系统而言，Lucene 非常好.基本上，Lucene 可以按照您希望的方式运行.您可以以编程方式与它交互以插入可索引的文档.同样，Google Appliance(不是 Google 自定义搜索)可以获得直接元供稿，这些供稿可以公开您想要编入索引的任何内容，例如直接来自数据库的数据.
As far as separate systems for handling search, Lucene is very good. Lucene works however you want it to work, essentially. You can interact with it programatically to insert indexable documents. Likewise, a Google Appliance (not Google Custom Search) can be given direct meta feeds which expose whatever you want to be indexed, such as data directly from a database.
这篇关于在后端为 MYSQL 的网站上集成搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
