Scenario
I have a search facility on a website that sells products.
The page is built with PHP and MySQL searching against a Merged View that joins 10+ tables together. There are approx 12,000 records in the view with 20+ fields each.
我在销售产品的网站上有搜索工具。该页面是使用PHP和MySQL构建的,它针对的是一个将10多个表连接在一起的Merged View。视图中有大约12,000条记录,每条记录有20多个字段。
A user can search for products matching a specific criteria using multiple (10-15) select menus.
The database then returns results based on the select menu values against the products primary keys.
用户可以使用多个(10-15)选择菜单搜索符合特定条件的产品。然后,数据库根据产品主键的选择菜单值返回结果。
This all works fine and at a fast enough speed as it is.
这一切都很好,并且速度足够快。
Problem
However, on the same page, I have also included a textbox so users can manually type in what they are searching for rather than using the menus.
但是,在同一页面上,我还包含了一个文本框,因此用户可以手动输入他们要搜索的内容而不是使用菜单。
As the users are typing actual words rather than selecting PK's via a menu, the database has to do a word search. For lack of knowledge on a better method, I have concantenated all of the text values from the foreign criteria tables into one big field.
The textbox then searches against words within this concantenated field.
当用户输入实际单词而不是通过菜单选择PK时,数据库必须进行单词搜索。由于缺乏关于更好方法的知识,我将外国标准表中的所有文本值都整合到一个大字段中。然后,文本框将搜索此concantenated字段中的单词。
Again, this works fine, but changes a typical search time from 0.1 seconds to 2.0 seconds.
I have indexed all of the fields from the tables used by the Merged View in an effort to improve times, this however, didn't help at all.
再次,这可以正常工作,但将典型的搜索时间从0.1秒更改为2.0秒。我已经将合并视图使用的表中的所有字段编入索引以努力改善时间,但这根本没有帮助。
Question
Since Google can find 22,200,000 pages in 1 second for the word "Overflow", and my database takes 2 seconds to search for a word against only 12,000 records.
由于谷歌可以在1秒内找到22,200,000页的“溢出”一词,而我的数据库需要2秒才能搜索一个单词,而不是12,000条记录。
How can I improve the layout and search method of the database?
如何改进数据库的布局和搜索方法?
4 个解决方案
#1
I would highly recommend Lucene Solr in this situation. It has a reverse index system that is very well-suited to this task. You can use it with PHP (you interface with it via frontend).
在这种情况下,我强烈推荐Lucene Solr。它有一个非常适合这项任务的反向索引系统。您可以将它与PHP一起使用(通过前端与它连接)。
Take a crack at it (at least look at some of the tutorials), and you'll be amazed at how quickly you are up and running.
仔细研究一下(至少看看一些教程),你会惊讶于你的运行速度。
#2
MySQL can do full-text search if you create a FULLTEXT index on one or more columns. From O'Reilly Databases:
如果在一列或多列上创建FULLTEXT索引,MySQL可以进行全文搜索。来自O'Reilly数据库:
FULLTEXTindices in MySQL allow database administrators and programmers to designate any character-based field (CHAR,VARCHAR, orTEXT) as aFULLTEXTindex, which allows for complex text searching against data stored in those fields.MySQL中的FULLTEXT索引允许数据库管理员和程序员将任何基于字符的字段(CHAR,VARCHAR或TEXT)指定为FULLTEXT索引,从而允许对存储在这些字段中的数据进行复杂的文本搜索。
This feature is not to be confused with the
LIKEfunction in MySQL.LIKEworks more along the lines of a regular expression. On the other hand,FULLTEXTindices are fully indexed fields which support stopwords, boolean searches, and relevancy ratings.不要将此功能与MySQL中的LIKE功能混淆。 LIKE更像是正则表达式。另一方面,FULLTEXT索引是完全索引的字段,支持停用词,布尔搜索和相关性评级。
#3
I have concantenated all of the text values from the foreign criteria tables into one big field. The textbox then searches against words within this concantenated field.
我已将外来条件表中的所有文本值连接到一个大字段中。然后,文本框将搜索此concantenated字段中的单词。
This makes no sense to me. Have you tried just using a WHERE LIKE on the appropriate fields? Am I missing something?
这对我来说毫无意义。您是否尝试在适当的字段上使用WHERE LIKE?我错过了什么吗?
#4
You should consider keeping the words in memory, so that you do not have to hit the DB every single time you need a word search. Assembling your words into a data structure would work, and you could simply build the data structure at deploy-time from your db.
您应该考虑将这些单词保留在内存中,这样您每次需要单词搜索时都不必点击数据库。将单词组装成数据结构是可行的,您可以在部署时从数据库构建数据结构。
#1
I would highly recommend Lucene Solr in this situation. It has a reverse index system that is very well-suited to this task. You can use it with PHP (you interface with it via frontend).
在这种情况下,我强烈推荐Lucene Solr。它有一个非常适合这项任务的反向索引系统。您可以将它与PHP一起使用(通过前端与它连接)。
Take a crack at it (at least look at some of the tutorials), and you'll be amazed at how quickly you are up and running.
仔细研究一下(至少看看一些教程),你会惊讶于你的运行速度。
#2
MySQL can do full-text search if you create a FULLTEXT index on one or more columns. From O'Reilly Databases:
如果在一列或多列上创建FULLTEXT索引,MySQL可以进行全文搜索。来自O'Reilly数据库:
FULLTEXTindices in MySQL allow database administrators and programmers to designate any character-based field (CHAR,VARCHAR, orTEXT) as aFULLTEXTindex, which allows for complex text searching against data stored in those fields.MySQL中的FULLTEXT索引允许数据库管理员和程序员将任何基于字符的字段(CHAR,VARCHAR或TEXT)指定为FULLTEXT索引,从而允许对存储在这些字段中的数据进行复杂的文本搜索。
This feature is not to be confused with the
LIKEfunction in MySQL.LIKEworks more along the lines of a regular expression. On the other hand,FULLTEXTindices are fully indexed fields which support stopwords, boolean searches, and relevancy ratings.不要将此功能与MySQL中的LIKE功能混淆。 LIKE更像是正则表达式。另一方面,FULLTEXT索引是完全索引的字段,支持停用词,布尔搜索和相关性评级。
#3
I have concantenated all of the text values from the foreign criteria tables into one big field. The textbox then searches against words within this concantenated field.
我已将外来条件表中的所有文本值连接到一个大字段中。然后,文本框将搜索此concantenated字段中的单词。
This makes no sense to me. Have you tried just using a WHERE LIKE on the appropriate fields? Am I missing something?
这对我来说毫无意义。您是否尝试在适当的字段上使用WHERE LIKE?我错过了什么吗?
#4
You should consider keeping the words in memory, so that you do not have to hit the DB every single time you need a word search. Assembling your words into a data structure would work, and you could simply build the data structure at deploy-time from your db.
您应该考虑将这些单词保留在内存中,这样您每次需要单词搜索时都不必点击数据库。将单词组装成数据结构是可行的,您可以在部署时从数据库构建数据结构。