Menu
We have a search engine on one of our websites that is extremely slow. I turned on slow query log and logged all queries that take longer then 10 seconds. Only queries from this search engine are being logged. Here is one example of the log:
# 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;
As you can see the query took roughly 22 seconds to execute, if do the same query directly in MySQL it takes about 4 seconds.
I did an EXPLAIN on this same query and the uotput is as follows:
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
Could anyone explain why the same query is sooo much slower on my website then direct in MySQL? Or even more important, is there a way to make this query faster on this website?
If you need more information to troubleshoot, give a shout and i will try to provide them.
Thanks so much in advance, Jan.
This is the PROFILE of the query:
+----------------------+----------+
| 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 |
+----------------------+----------+
382
Queries can become slow for various reasons ranging from improper index usage to bugs in the storage engine itself. However, in most cases, queries become slow because developers or MySQL database administrators neglect to monitor them and keep an eye on their performance.
To enable the slow query log, type the following command at the mysql> prompt: Copy SET GLOBAL slow_query_log = 'ON'; There are additional options that you can set for the slow query log: By default, when the slow query log is enabled, it logs any query that takes longer than 10 seconds to run.
I see lots of non-indexable conditions such as Firstname LIKE '%statutair%'. A possibility is that is MySQL is actually using its results cache, while PHP is not able to leverage the results cache. This can happen, e.g., if you use PDO with the ATTR_EMULATE_PREPARES option disabled.
To find out, add the SQL_NO_CACHE clause when testing from within MySQL, as in:
SELECT SQL_NO_CACHE id, name FROM customer;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
