mysql 存储例程与 mysql 替代方案？
我们正在使用总共大约 150,000 条记录（名称）的 mysql 数据库。我们对“姓名”字段的搜索是通过 php 中的自动完成功能完成的。我们已经对表格建立了索引，但仍然觉得搜索有点缓慢（几秒钟的时间，而谷歌财经之类的东西则几乎即时响应）。我们提出了两种可能性，但希望获得更多见解：
我们可以创建一堆（数千或更多）存储过程来加速搜索，还是创建这么多存储过程会使数据库陷入困境？
对于“select”语句，是否有比mysql更快的替代方案（插入和更新行的速度并不是太重要，因此如果有必要，我们可以牺牲它）。我隐约听说过 BigTable 和 BigTable。其他不支持 JOIN 语句....我们需要 JOIN 语句来执行我们所做的一些其他查询。
谢谢
We are using a mysql database w/ about 150,000 records (names) total. Our searches on the 'names' field is done through an autocomplete function in php. We have the table indexed but still feel that the searching is a bit sluggish (a few full seconds vs. something like Google Finance w/ near-instant response). We came up w/ 2 possibilities, but wanted to get more insight:
Can we create a bunch (many thousands or more) of stored procedures to speed up searches, or will creating that many stored procedures bog-down the db?
Is there a faster alternative to mysql for "select" statements (speed on inserting & updating rows isn't too important so we can sacrifice that, if necessary). I've vaguely heard of BigTable & others that don't support JOIN statements....we need JOIN statements for some of our other queries we do.
thx
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您提到的异常查询执行时间是服务器配置错误或数据库架构错误或两者兼而有之的结果。请阅读有关 serverfault 的此回复或更新您的问题此处：提供服务器配置、部分数据库架构和问题查询以及
explain select ...Abnormal query execution time you mentioned is a result of server misconfiguration or wrong database schema, or both. Please read this response on serverfault or update your question here: provide server configuration, part of database schema and problem query along with
explain select ...您需要将信息缓存在内存中，以避免重复调用数据库。
You need to cache the information in memory to avoid making repeated calls to the database.
是的，如果更改数据，您需要使缓存过期，但正如您所说，这并不常见，因此您甚至可以半自动地执行此操作，并且在必要时不必担心。您应该查看这篇 MySQL.com 文章，如下所示也许还可以探索 MEMORY 存储引擎（抱歉，新的，每个帖子不能发布多个超链接？！），该引擎需要一些编码才能使用，但效率极高。
实际查询时间（相对于页面时间）是多少？在一个没有加载到地狱的相当现代的服务器上，MySQL 应该能够对 150k 行执行自动完成查询，速度比两秒快得多。缺少一些索引吗？
Yes, you need to expire the cache if you change the data, but as you said, that's not common, so you can even do that on a semi-automated basis and not worry about it if necessary. You should check out this MySQL.com article, as well as perhaps explore the MEMORY storage engine (sorry, new and can't post more than one hyperlink per post?!) which takes a little bit of coding around to use but can be extremely efficient.
What's the actual query time (vs page time)? On a reasonably modern server that's not loaded to hell, MySQL should be able to do an autocomplete query on 150k rows much, much, faster than two seconds. Missing some indexes?