我的客户有一个仅包含三个字段一个庞大的数据库：

在主键（一个无符号数） 在名称（多字的文字） 在说明（1000 VARCHAR）

该数据库已经得到了数十亿项。我在处理数据等大量没有previous经验。

他要我来设计使用AJAX（如谷歌）搜索该数据库的接口。我的疑问是因为乌龟一样慢。

什么是搜索文本框在这样一个大的数据库最好的方法？如果用户在界面输入错误的拼写，我怎么能回到他想要的东西？

解决方案

如果您使用的是FULLTEXT索引，你正确输入查询，并在返回结果的速度是不够的，你正在进入一个领土其中，MySQL可能根本无法满足你..

您的可以的可以调整设置，购买足够的内存，以确保您的整个数据集装入内存100％。这绝对是真的，性能提升是巨大的存在。

我肯定会推荐寻找到你的MySQL配置的调整。我们已经在过去的一些愚蠢的设置。操作系统的缺省值趋向于真闹心！

不过，如果你有麻烦，在这一点上，您可以：

创建包含每个字（索引）以及一个记录ID，它指的是一个单独的表。这将允许你搜索一个词。 使用这对解决该问题的最优化的不同系统。除非我的信息是现在已经过时，2个引擎是最流行的解决这个问题是： 在狮身人面像 的Solr / Lucene的

My client has a huge database containing just three fields:

Primary key (a unsigned number) Name (multi-word text) Description (up to 1000 varchar)

This database has got over few billion entries. I have no previous experience in handling such large amounts of data.

He wants me to design an interface using AJAX (like Google) to search this database. My queries are as slow as turtle.

What is best way to search text fields in such a large database? If the user is typing wrong spelling on interface, how can I return what he wanted ?

解决方案

If you are using FULLTEXT indexes, you're correctly writing your queries, and the speed in which the results are returned are not adequate, you are entering a territory where MySQL may simply not be sufficient for you..

You may be able to tweak settings, purchase enough RAM to make sure that your entire data-set fits 100% in memory. It's definitely true that performance gains could be huge there.

I'd definitely recommend looking into tweaks of your mysql configuration. We've had some silly settings in the past. Operating system defaults tend to really suck!

However, if you have trouble at that point, you can:

Create a separate table containing each word (indexed) along with a record id that it refers to. This will allow you to search on single words. Use a different system that's optimized for solving this problem. Unless my information is now outdated, the 2 engines that are the most popular for solving this problem are:

Sphinx Solr / Lucene