Query fast in MySQL but slow in PHP

Question

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 |
+----------------------+----------+

Welcome to SO, congrats on a very well presented first question :) — Dale, Aug 01 '12 at 10:05
If the data being passed back is significant, it will take time for it to be returned back into your php application. I would seriously recommend setting up an sqlfiddle with this question as your query is huge and hard to break down without seeing it in action (or at least for me it is) — Lee, Aug 01 '12 at 10:05
Add a LIMIT clause and only bring back the first 10 results, does that speed up the data transfer issue? — Cups, Aug 01 '12 at 10:09
@Lee & Cups, according to MiDo's comment (pretty nice catch) `Rows_sent: 33`. OP, how about [`PROFILE`ing the query](https://dev.mysql.com/doc/refman/5.0/en/show-profiles.html), check if you see anything strange. Also, since you're experiencing the problem when connecting from PHP, can we see your connection code (maybe you're setting some attribute or something..) — Adi, Aug 01 '12 at 10:10
@Adnan, I pasted the query profile in my original post. It was too long to fit in a comment. — Jan, Aug 01 '12 at 12:38
Bravo for the query. Can't you use a keyword table that you fill with keywords linked to the row id of the original item. Then you only need to search in the keyword table to get the results. — huysentruitw, Aug 01 '12 at 16:32

score 2 · Answer 1 · edited May 23 '17 at 12:07

2

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;

edited May 23 '17 at 12:07

Community

1
1

answered Aug 01 '12 at 10:18

Álvaro González

142,137
41
261
360

Hi Álvaro. The mentioned 4s I gave was a non cached result. If i re-query in MySQL, results shows up in about 30ms. The website seems to be caching too. If i use the same search word again, results come pretty fast on screen. – Jan Aug 01 '12 at 11:27
@Jan - Well, it was just a shot in the dark :) – Álvaro González Aug 01 '12 at 11:35

score 0 · Accepted Answer · answered Aug 01 '12 at 16:24

It seems to me that you have to set an index on a lot of columns. Please read this: http://dev.mysql.com/doc/refman/5.0/en/explain-output.html To make the query faster, start by setting an index on every column that you use in your JOIN statements. If that does not speed things up, set an index on columns you check your WHERE's against. Also, the OR operator is slow, and in the block with all the OR's there is also an AND. And maybe you can skip a few of those OR's, like 'initials' (but that is of course a wild guess...)

Query fast in MySQL but slow in PHP

2 Answers2