Body: <p>I’m looking for a way to make a database query faster. The table <code>parts</code> has over 6 million rows. Each part row is associated with the <code>manufacturers</code> table (a couple hundred rows) by ID. When searching for results matching a query in a specific column (i.e.: partNumber or materialDescription) I have efficient queries that return results quickly.</p>
  
  <p>The problem I’m having is when I’m doing an ALL search where the result can be in the <code>parts</code> table’s <code>partNumber</code> column or <code>materialDescription</code> column or the manufacturer’s title (which can only be determined by the <code>manufacturerID</code> from the part’s row which gets the <code>manufacturerCode</code> from the <code>manufacturers</code> table which can then get the manufacturer’s actual name by finding the entry in Craft’s content table <code>field_manufacturerCode</code> column. The results time is well past 20 seconds. Is there any way to make this database query more efficient assuming we can’t add the actual manufacturer’s name to the <code>manufacturers</code> table because the API we are getting the data from doesn’t include the manufacturer’s name?</p>
  
  <pre><code>SELECT p.id, p.manufacturerId, m.code as manufacturerCode, p.materialNumber, p.partNumber, p.materialDescription, c.title as manufacturerTitle
  FROM `craft__parts` AS p
  JOIN `craft__manufacturers` AS m ON p.manufacturerId = m.id
  JOIN `craft_content` AS c ON m.code = c.field_manufacturerCode
  WHERE p.partNumber like '%polymer%'
  OR p.materialDescription like '%polymer%'
  OR c.title like '%polymer%'
  ORDER BY manufacturerTitle asc
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-21T16:26:43.427Z
Comments.Text: This is going to be near impossible to help with in its current form. And honestly you'll probably be better off posting to something like dba.stackexchange.com as it's more of a query tuning question than directly related to Craft (and there are a lot more database experts there). If you go that route, I'd suggest sharing DDL for all involved tables including their indexes.
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-21T22:32:27.527Z
Comments.Text: Thanks, I've provided more information on the dba.stackexchange like you suggested: http://dba.stackexchange.com/questions/118801/faster-mysql-query-when-joining-data-from-multiple-tables
Comments.UserDisplayName: Kyle
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-21T22:58:24.087Z
Comments.Text: Two comments already... nice. :)
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-22T07:38:08.427Z
Comments.Text: This question belongs on another site in the Stack Exchange network: dba.stackexchange.com
Comments.UserDisplayName: carlcs
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-23T22:22:51.617Z
Comments.Text: Closing because this was cross-posted (and is a better fit on dba.SE)
Comments.UserDisplayName: Lindsey D
Tags: <plugin-development><performance><mysql>
Title: Faster MySQL query when joining data from a non-Craft table to a Craft table
ThreadText: # Faster MySQL query when joining data from a non-Craft table to a Craft table
  
  (Asked by: username_0 on 2015-10-21)
  
  I’m looking for a way to make a database query faster. The table `parts` has over 6 million rows. Each part row is associated with the `manufacturers` table (a couple hundred rows) by ID. When searching for results matching a query in a specific column (i.e.: partNumber or materialDescription) I have efficient queries that return results quickly.
  
  The problem I’m having is when I’m doing an ALL search where the result can be in the `parts` table’s `partNumber` column or `materialDescription` column or the manufacturer’s title (which can only be determined by the `manufacturerID` from the part’s row which gets the `manufacturerCode` from the `manufacturers` table which can then get the manufacturer’s actual name by finding the entry in Craft’s content table `field_manufacturerCode` column. The results time is well past 20 seconds. Is there any way to make this database query more efficient assuming we can’t add the actual manufacturer’s name to the `manufacturers` table because the API we are getting the data from doesn’t include the manufacturer’s name?
  
  ```
  SELECT p.id, p.manufacturerId, m.code as manufacturerCode, p.materialNumber, p.partNumber, p.materialDescription, c.title as manufacturerTitle
  FROM `craft__parts` AS p
  JOIN `craft__manufacturers` AS m ON p.manufacturerId = m.id
  JOIN `craft_content` AS c ON m.code = c.field_manufacturerCode
  WHERE p.partNumber like '%polymer%'
  OR p.materialDescription like '%polymer%'
  OR c.title like '%polymer%'
  ORDER BY manufacturerTitle asc
  
  ```
  
  **Tags:** plugin-development, performance, mysql
  
  > **Comments:**
  > **username_1**: This is going to be near impossible to help with in its current form. And honestly you'll probably be better off posting to something like dba.stackexchange.com as it's more of a query tuning question than directly related to Craft (and there are a lot more database experts there). If you go that route, I'd suggest sharing DDL for all involved tables including their indexes.  
  > **username_0**: Thanks, I've provided more information on the dba.stackexchange like you suggested: http://dba.stackexchange.com/questions/118801/faster-mysql-query-when-joining-data-from-multiple-tables  
  > **username_1**: Two comments already... nice. :)  
  > **username_2**: This question belongs on another site in the Stack Exchange network: dba.stackexchange.com  
  > **username_3**: Closing because this was cross-posted (and is a better fit on dba.SE)  
  
  ---
  
  ## 0 answers
  
  