更快的 MySQL 查询?
在我的工作中,我有几个包含超过 200,000 行数据的表。我必须设置一些一次查看超过 15,000 个以上的查询,所以有时我会收到此错误:
PHP 致命错误:最大执行次数 时间超过180秒
那么,如何加快更快的查询速度呢?
查询是这样的:
SELECT toemail, toname
FROM email_sent
WHERE companyid = '$member[companyid]'
谢谢。
At my work I have several tables with over 200,000 rows of data. I have to set-up some queries that look over 15,000+ at a time so sometimes I get this error:
PHP Fatal error: Maximum execution
time of 180 seconds exceeded
So, how do I speed up faster queries?
The query is like this:
SELECT toemail, toname
FROM email_sent
WHERE companyid = '$member[companyid]'
Thanks.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
在
email_sent (company_id)
上创建索引:Create an index on
email_sent (company_id)
:优化可能就是答案。如果还不够,您可以随时增加 PHP 的时间限制。
这将为该脚本设置它:
set_time_limit docs
或者,编辑 php.ini 并更改 max_execution_time 设置。当然,这将在全球范围内改变它。听起来它已经被调整了(由你的系统管理员?),因为默认值为 30 秒。
Optimization might be the answer. If it's not enough, you can always just increase PHP's time limit.
This will set it for just that script:
set_time_limit docs
Or, edit php.ini and change the max_execution_time setting. This will change it globally, of course. It sounds like it has already been adjusted (by your sysadmin?) as the default is 30 seconds.
添加索引(如果尚未添加)。
另一种方法是从MyISAM切换到InnoDB。
Adding an index if you haven't already.
Another way is to switch from MyISAM to InnoDB.
您可能想要研究的第一件事是对参与查询的任何列建立索引。例如,如果您的查询始终测试列
FirstName
的值,您可能需要为其建立索引。如果您提供 DDL(数据定义语言)脚本或表的描述,以及花费很长时间的查询,我们也许能够提供更好的索引提示。
如果您已经尽可能地进行了调整,但仍然出现超时,您可能想看看是否可以增加事务超时限制。我对您的服务器设置了解不够,无法提供详细信息,但这种情况通常是可能的。
更新
如果您的查询是:
我的第一个问题是:您在
companyid
上有索引吗?如果没有,创建索引是否会提高性能?The first thing you might want to look into is indexing any columns which participate in the query. For example, if you your query is always testing the value of a column
FirstName
, you might want to index that.If you provide a DDL (Data Definition Lanaguage) script or a description of the tables, as well as the queries that are taking so long, we might be able to provide better tips for indexing.
If you've already tuned as much as you can and you still get timeouts, you might want to see if you can increase the transaction timeout limit. I don't know enough about your server setup to give details, but that sort of thing is usually possible.
UPDATE
If your query is:
My first question is: do you have an index on
companyid
and if not, does creating one improve performance?