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