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

CC BY-SA 3.0
2015-10-21T16:26:43.427Z
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.
Brad Bell
CC BY-SA 3.0
2015-10-21T22:32:27.527Z
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
Kyle
CC BY-SA 3.0
2015-10-21T22:58:24.087Z
Two comments already... nice. :)
Brad Bell
CC BY-SA 3.0
2015-10-22T07:38:08.427Z
This question belongs on another site in the Stack Exchange network: dba.stackexchange.com
carlcs
CC BY-SA 3.0
2015-10-23T22:22:51.617Z
Closing because this was cross-posted (and is a better fit on dba.SE)
Lindsey D
<plugin-development><performance><mysql>
Faster MySQL query when joining data from a non-Craft table to a Craft table
# 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

