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
  • 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.
    – Brad Bell
    Oct 21 '15 at 16:26
  • Thanks, I've provided more information on the dba.stackexchange like you suggested: dba.stackexchange.com/questions/118801/…
    – Kyle
    Oct 21 '15 at 22:32
  • Two comments already... nice. :)
    – Brad Bell
    Oct 21 '15 at 22:58
  • 2
    This question belongs on another site in the Stack Exchange network: dba.stackexchange.com
    – carlcs
    Oct 22 '15 at 7:38
  • Closing because this was cross-posted (and is a better fit on dba.SE)
    – Lindsey D
    Oct 23 '15 at 22:22