I have a query that uses SUBSTRING() as a criterion:
SELECT p.name p_name, pa.line1 p_line1, pa.zip p_zip, c.name c_name, ca.line1 c_line1, ca.zip c_zip FROM bank b JOIN import_bundle ib ON ib.bank_id = b.id JOIN generic_import gi ON gi.import_bundle_id = ib.id JOIN account_import ai ON ai.generic_import_id = gi.id JOIN account a ON a.account_import_id = ai.id JOIN account_address aa ON aa.account_id = a.id JOIN address ca ON aa.address_id = ca.id JOIN address pa ON pa.zip = ca.zip OR (pa.zip = ca.zip AND pa.line1 = ca.line1) JOIN prospect p ON p.address_id = pa.id JOIN customer c ON a.customer_id = c.id WHERE b.name = 'M' AND ib.active = 1 AND gi.active = 1 AND SUBSTRING(p.name, 1, 12) = SUBSTRING(c.name, 1, 12) LIMIT 100
As you can see, this is just a comparison of the first 12 characters of p.name and c.name . Unfortunately, adding this request to the WHERE makes my request unbearably slow. Are there any tricks to make the same comparison, or is this the best way to add another column to each table that contains the first 12 characters of the client name? I hope this is not the last, because it will be a lot of work, and I will eventually make several comparisons like this.
source share