title: Speed up Mysql Query using a split function
body: <p>I am trying to speed up a MYSQL query. 
  In a column called "MISC", I first have to extract a "traceID" variable, that will be used to match row of another table.
  Example of the MISC column: </p>
  
  <pre><code>PFFCC_Strip/fkk49322/PMethod=Diners/CardType=Diners/9999******9999/2010/TraceId=7122910
  </code></pre>
  
  <p>I am extracting the value "7122910" as traceID and find corresponding row with a left join. The traceId value being unique, only one row must be present on each table.</p>
  
  <p>I cannot set Index on the tables to speed up process. Any approach that could make this query run faster? As it is, it takes a few seconds to run which is not possible.</p>
  
  <pre><code>select *
  from
  (select TraceID,PP,UDef2, Payment_Method, Approved, TransactionID, Amount
  from pr) pr
  left join 
  (select 
   PAYMENT_ID as Payment_ID_omega, TRANSACTION_TYPE, 
   REQUESTED_AMOUNT, AMOUNT, `STATUS` as StatusRef_omega,
   REQUEST_DATE, Agent,
   if (locate('TraceId=',MISC)&gt;0, SUBSTRING_INDEX(MISC,'TraceId=',-1),'') as traceID
  from BankingActivity ) omega
  on pr.TraceID = omega.traceID
  having
  (REQUEST_DATE BETWEEN DATE_ADD(DATE(NOW()), INTERVAL -1 DAY) AND NOW())
  ORDER BY pr.TraceID DESC
  </code></pre>