title: SQL to compare records with and without given string is very slow, IS it possible to optimize?
body: <p>I have this query.</p>
  <pre><code>    SELECT WithoutX.Field1, WithoutX.Field3 ,AVG(WithoutX.Field2) AS WO_X, AVG(withX.Field2) AS with_X 
  FROM (
      SELECT ROUND(Table1.Field3,1) as Field3,Table3.Field2,Table2.Field1
      FROM Table1 
      INNER join Table2 
      ON Table1.Field5 = Table2.Field5 AND instr (Table2.Field1,'X')&gt;0
      LEFT JOIN Table3 ON Table3.Field4 = Table1.Field4
      ) AS withX 
   INNER JOIN (
      SELECT ROUND(Table1.Field3,1) as Field3,Table3.Field2,Table2.Field1
      FROM Table1 
      INNER join Table2 
      ON Table1.Field5 = Table2.Field5 AND instr (Table2.Field1,'X')=0
      LEFT JOIN Table3 ON Table3.Field4 = Table1.Field4
      ) AS WithoutX ON withX.Field3 = WithoutX.Field3 AND removeX(withX.Field1,'X') = WithoutX.Field1 
   GROUP BY WithoutX.Field1, WithoutX.Field3
  </code></pre>
  <p>removeX is a function that removes the substring x and other parts of the string in field1, so that the two derived tables WithoutX and WithX can be join based on field1.
  It's working but it's very slow. I checked each parts separately and it seams the slowing down comes from the main INNER Join and the Group By. But the function does not change much the speed. Is there any way to optimize this query ?</p>