我们的一个网站上有一个非常慢的搜索引擎。我打开了慢速查询日志并记录了所有超过 10 秒的查询。 仅记录来自该搜索引擎的查询。 这是日志的一个示例:
# Time: 120801 9:21:42
# User@Host: ********** @ localhost []
# Query_time: 22.156250 Lock_time: 0.000000 Rows_sent: 33 Rows_examined: 3385401
SET timestamp=1343805702;
SELECT *, IF(InSection OR InBranche, 1, 0) AS SorteerKolom FROM(SELECT DISTINCT Plant, Email, Nicename, Displayname, JobTitle, Department, Initials, Lastname, LastnameForSort,
search_people.ForeignId, IsVennoot,
(Zoekwoorden LIKE '%statutair%') AS SearchTerm,
(Displayname LIKE '%statutair%') AS ByName,
0 AS InSection, 0 AS InBranche, 1 AS ShowAll,
(SELECT COUNT(*) FROM search_hasarticles WHERE UserId = search_people.ForeignId) > 0 AS HasWritten
FROM search_people
LEFT JOIN search_people_branches ON search_people.ForeignId = search_people_branches.UserId
LEFT JOIN search_people_specialismen ON search_people.ForeignId = search_people_specialismen.UserId
LEFT JOIN wp_usermeta AS wpu ON (wpu.user_id = search_people.ForeignId)
WHERE
(
Firstname LIKE '%statutair%'
OR Lastname LIKE '%statutair%'
AND Displayname LIKE '%statutair%'
OR Email LIKE '%statutair%'
OR Address LIKE '%statutair%'
OR Initials LIKE '%statutair%'
OR Location LIKE '%statutair%'
OR Givenname LIKE '%statutair%'
OR Nickname LIKE '%statutair%'
OR JobTitle LIKE '%statutair%'
OR Login LIKE '%statutair%'
OR Title LIKE '%statutair%'
OR Phone LIKE '%statutair%'
OR Fax LIKE '%statutair%'
OR Plant LIKE '%statutair%'
OR Displayname LIKE '%statutair%'
OR Zoekwoorden LIKE '%statutair%'
)
AND (1=1) AND search_people.IsHidden = 0 AND search_people.Activated = 1 UNION SELECT DISTINCT Plant, Email, Nicename, Displayname, JobTitle, Department, Initials, Lastname, LastnameForSort,
search_people.ForeignId, IsVennoot, 0 AS SearchTerm, 0 AS ByName, 0 AS InSection, 1 AS InBranche, 1 AS ShowAll,
(SELECT COUNT(*) FROM search_hasarticles WHERE UserId = search_people.ForeignId) > 0 AS HasWritten
FROM search_people
LEFT JOIN search_people_branches ON search_people.ForeignId = search_people_branches.UserId
LEFT JOIN search_branches ON search_branches.ForeignId = search_people_branches.BrancheId
LEFT JOIN search_people_specialismen ON search_people.ForeignId = search_people_specialismen.UserId
WHERE Name LIKE '%statutair%' AND (1=1) AND search_people.IsHidden = 0 AND search_people.Activated = 1 UNION SELECT DISTINCT Plant, Email, Nicename, Displayname, JobTitle, Department, Initials, Lastname, LastnameForSort, search_people.ForeignId, IsVennoot, 0 AS SearchTerm, 0 AS ByName, 1 AS InSection, 0 AS InBranche, 1 AS ShowAll, (SELECT COUNT(*) FROM search_hasarticles WHERE UserId = search_people.ForeignId) > 0 AS HasWritten FROM search_people LEFT JOIN search_people_specialismen ON search_people.ForeignId=search_people_specialismen.UserId LEFT JOIN search_specialties ON search_specialties.ForeignId=search_people_specialismen.SpecialismeId LEFT JOIN search_people_branches ON search_people.ForeignId=search_people_branches.UserId WHERE (Name LIKE '%statutair%' OR SearchTerms LIKE '%statutair%') AND (1=1) AND search_people.IsHidden = 0 AND search_people.Activated = 1 UNION SELECT DISTINCT Plant, Email, Nicename, Displayname, JobTitle, Department, Initials, Lastname, LastnameForSort, search_people.ForeignId, IsVennoot, 0 AS SearchTerm, 0 AS ByName, 0 AS InSection, 0 AS InBranche, 0 AS ShowAll, 1 AS HasWritten FROM search_posts LEFT JOIN search_posts_branches ON search_posts.ForeignId=search_posts_branches.PostId LEFT JOIN search_branches ON search_posts_branches.BrancheId=search_branches.ForeignId LEFT JOIN search_people_specialismen ON search_posts.PostAuthor=search_people_specialismen.UserId LEFT JOIN search_specialties ON search_people_specialismen.SpecialismeId=search_specialties.ForeignId INNER JOIN search_people ON search_people.ForeignId=search_posts.PostAuthor WHERE (PostTitle LIKE '%statutair%' OR PostContent LIKE '%statutair%' OR search_branches.Name LIKE '%statutair%' OR search_specialties.Name LIKE '%statutair%') AND PostStatus='publish' AND PostType='post' AND (1=1) AND search_people.IsHidden = 0 AND search_people.Activated = 1) AS search_results ORDER BY SearchTerm DESC, ByName DESC, SorteerKolom DESC, IsVennoot DESC, InSection DESC, InBranche DESC, HasWritten DESC, LastnameForSort ASC, Initials ASC;
如您所见，执行查询大约需要 22 秒，如果直接在 MySQL 中执行相同的查询，大约需要 4 秒。
我对同一个查询做了 EXPLAIN，结果如下:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 33 Using filesort
2 DERIVED search_people ALL NULL NULL NULL NULL 323 Using where; Using temporary
2 DERIVED search_people_branches ALL NULL NULL NULL NULL 2013 Distinct
2 DERIVED search_people_specialismen ALL NULL NULL NULL NULL 1013 Distinct
2 DERIVED wpu ref user_id user_id 8 wordpress.search_people.ForeignId 84 Using index; Distinct
3 DEPENDENT SUBQUERY search_hasarticles ALL NULL NULL NULL NULL 101 Using where
4 UNION search_branches ALL NULL NULL NULL NULL 19 Using where; Using temporary
4 UNION search_people ALL NULL NULL NULL NULL 323 Using where; Using join buffer
4 UNION search_people_specialismen ALL NULL NULL NULL NULL 1013 Distinct
4 UNION search_people_branches ALL NULL NULL NULL NULL 2013 Using where; Distinct; Using join buffer
5 DEPENDENT SUBQUERY search_hasarticles ALL NULL NULL NULL NULL 101 Using where
6 UNION search_specialties ALL NULL NULL NULL NULL 73 Using where; Using temporary
6 UNION search_people ALL NULL NULL NULL NULL 323 Using where; Using join buffer
6 UNION search_people_specialismen ALL NULL NULL NULL NULL 1013 Using where; Distinct; Using join buffer
6 UNION search_people_branches ALL NULL NULL NULL NULL 2013 Distinct
7 DEPENDENT SUBQUERY search_hasarticles ALL NULL NULL NULL NULL 101 Using where
8 UNION search_posts ALL NULL NULL NULL NULL 15860 Using where; Using temporary
8 UNION search_posts_branches ALL NULL NULL NULL NULL 149 Distinct
8 UNION search_branches ALL NULL NULL NULL NULL 19 Distinct
8 UNION search_people_specialismen ALL NULL NULL NULL NULL 1013 Distinct
8 UNION search_specialties ALL NULL NULL NULL NULL 73 Using where; Distinct
8 UNION search_people ALL NULL NULL NULL NULL 323 Using where; Distinct; Using join buffer
UNION RESULT <union2,4,6,8> ALL NULL NULL NULL NULL NULL
谁能解释为什么同样的查询在我的网站上比直接在 MySQL 中慢得多？或者更重要的是，有没有办法让这个网站上的查询更快？
如果您需要更多信息来进行故障排除，请大声说出来，我会尽力提供。
非常感谢， 一月
这是查询的 PROFILE:
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000431 |
| Opening tables | 0.002004 |
| System lock | 0.000012 |
| Table lock | 0.000502 |
| optimizing | 0.000033 |
| statistics | 0.000042 |
| preparing | 0.000034 |
| Creating tmp table | 0.000085 |
| executing | 0.000004 |
| Copying to tmp table | 0.001957 |
| Sending data | 0.000006 |
| optimizing | 0.000025 |
| statistics | 0.000027 |
| preparing | 0.000041 |
| Creating tmp table | 0.000114 |
| executing | 0.000001 |
| Copying to tmp table | 0.000058 |
| Sending data | 0.000004 |
| optimizing | 0.000021 |
| statistics | 0.000027 |
| preparing | 0.000037 |
| Creating tmp table | 0.000095 |
| executing | 0.000003 |
| Copying to tmp table | 0.007376 |
| optimizing | 0.000013 |
| statistics | 0.000017 |
| preparing | 0.000011 |
| executing | 0.000005 |
| Sending data | 0.001248 |
| executing | 0.000014 |
| Sending data | 0.001894 |
| executing | 0.000007 |
| Sending data | 0.003249 |
| executing | 0.000004 |
| Sending data | 0.001487 |
| executing | 0.000001 |
| Sending data | 0.000433 |
| executing | 0.000001 |
| Sending data | 0.012100 |
| executing | 0.000006 |
| Sending data | 0.000713 |
| executing | 0.000002 |
| Sending data | 0.000681 |
| executing | 0.000001 |
| Sending data | 0.015382 |
| executing | 0.000005 |
| Sending data | 0.001048 |
| executing | 0.000002 |
| Sending data | 0.000916 |
| executing | 0.000004 |
| Sending data | 0.000421 |
| executing | 0.000001 |
| Sending data | 0.000561 |
| executing | 0.000001 |
| Sending data | 0.005126 |
| executing | 0.000008 |
| Sending data | 0.014534 |
| executing | 0.000004 |
| Sending data | 0.001666 |
| executing | 0.000006 |
| Sending data | 0.001641 |
| Sending data | 0.000203 |
| optimizing | 0.000045 |
| statistics | 0.000050 |
| preparing | 0.000049 |
| Creating tmp table | 0.000182 |
| executing | 0.000002 |
| Copying to tmp table | 5.101209 |
| Sending data | 0.000226 |
| optimizing | 0.000007 |
| statistics | 0.000008 |
| preparing | 0.000007 |
| executing | 0.000001 |
| Sending data | 0.000217 |
| removing tmp table | 0.000044 |
| Sending data | 0.000007 |
| removing tmp table | 0.000012 |
| Sending data | 0.000017 |
| removing tmp table | 0.000011 |
| Sending data | 0.000005 |
| removing tmp table | 0.000033 |
| Sending data | 0.000008 |
| removing tmp table | 0.000030 |
| Sending data | 0.000009 |
| init | 0.000044 |
| optimizing | 0.000005 |
| statistics | 0.000004 |
| preparing | 0.000007 |
| executing | 0.000002 |
| Sorting result | 0.000074 |
| Sending data | 0.000164 |
| end | 0.000003 |
| query end | 0.000005 |
| freeing items | 0.000210 |
| removing tmp table | 0.000061 |
| closing tables | 0.000051 |
| logging slow query | 0.000003 |
| cleaning up | 0.000026 |
+----------------------+----------+
最佳答案
我看到很多不可索引的条件，例如 Firstname LIKE '%statutair%'。一种可能是 MySQL 实际上正在使用它的 results cache ，而 PHP 无法利用结果缓存。这可能会发生，例如，如果您将 PDO 与 ATTR_EMULATE_PREPARES option disabled 一起使用.
要找出答案，请在从 MySQL 中进行测试时添加 SQL_NO_CACHE 子句，如下所示:
SELECT SQL_NO_CACHE id, name FROM customer;
关于php - 在 MySQL 中查询速度快，但在 PHP 中查询速度慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/11756932/
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时
我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin
我在Rails上使用带有ruby的solr。一切正常，我只需要知道是否有任何现有代码来清理用户输入，比如以?开头的查询。或* 最佳答案 我不知道执行此操作的任何代码，但理论上可以通过查看parsingcodeinLucene来完成并搜索thrownewParseException(只有16个匹配!)。在实践中，我认为您最好只捕获代码中的任何solr异常并显示“无效查询”消息或类似信息。编辑:这里有几个“sanitizer”:http://pivotallabs.com/users/zach/blog/articles/937-s
我正在为锦标赛开发一个Rails应用程序。我在这个查询中使用了三个模型:classPlayertruehas_and_belongs_to_many:tournamentsclassTournament:destroyclassPlayerMatch"Player",:foreign_key=>"player_one"belongs_to:player_two,:class_name=>"Player",:foreign_key=>"player_two"在tournaments_controller的显示操作中，我调用以下查询:Tournament.where(:id=>params
我想用sunspot重现以下原始solr查询q=exact_term_text:fooORterm_textv:foo*ORalternate_text:bar*但我无法通过标准的太阳黑子界面理解这是否可能以及如何实现，因为看起来:fulltext方法似乎不接受多个文本/搜索字段参数我不知道将什么参数作为第一个参数传递给fulltext，就好像我通过了"foo"或"bar"结果不匹配如果我传递一个空参数，我得到一个q=*:*范围过滤器(例如with(:term).starting_with('foo*')(顾名思义)作为过滤器查询应用，因此不参与评分。似乎可以手动编写字符串(或者可能使
我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi
例如，假设我有一个名为Products的模型，并且在ProductsController中，我有以下代码用于product_listView以显示已排序的产品。@products=Product.order(params[:order_by])让我们想象一下，在product_listView中，用户可以使用下拉菜单按价格、评级、重量等进行排序。数据库中的产品不会经常更改。我很难理解的是，每次用户选择新的order_by过滤器时，rails是否必须查询，或者rails是否能够以某种方式缓存事件记录以在服务器端重新排序？有没有一种方法可以编写它，以便在用户排序时rails不会重新查询结果