title: Query fast in MySQL but slow in PHP
body: <p>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:</p>
  
  <pre><code># 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) &gt; 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) &gt; 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) &gt; 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;
  </code></pre>
  
  <p>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.</p>
  
  <p>I did an EXPLAIN on this same query and the uotput is as follows:</p>
  
  <pre><code>id                               select_type                      table                            type                             possible_keys                    key                              key_len                          ref                               rows                             Extra                                    
  1                                PRIMARY                          &lt;derived2&gt;                       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                     &lt;union2,4,6,8&gt;                   ALL                              NULL                             NULL                             NULL                             NULL                              NULL                                                                      
  </code></pre>
  
  <p>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?</p>
  
  <p>If you need more information to troubleshoot, give a shout and i will try to provide them.</p>
  
  <p>Thanks so much in advance,
  Jan.</p>
  
  <p>This is the PROFILE of the query:</p>
  
  <pre><code>+----------------------+----------+
  | 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 |
  +----------------------+----------+
  </code></pre>