Order of table processing in joins

• • Reply

  16 years ago

  by harmonycitra

  Chitra Vijay

  , India

  Joined 17 years ago

  Actually I'm accessing mySQL Table from Crystal Reports.
  One of report works in a strange fashion.
  The reason it the query passed to mySQL
  

  
  The sample query is
  

  
  SELECT c.action,c.datetime, b.collector, c, a.ddloc, a.debtorno
  FROM   c,b,a WHERE  (c.debtorno=b.debtorno)
  AND (c.debtorno=a.debtorno) AND b.collector='XA1' AND (c.datetime>={ts '2004-09-20 00:00:00'} AND c.datetime<{ts '2004-09-21 00:00:00'}) AND c.action='TA'
  

  
  By explaining the query using "EXPLAIN" command, I got strange results.
  

  
  During the first time it gives the folg. result
  

  
  

  
  [Row,  table,  type,  possiblekeys,  key,  keylen,  ref,  rows,  Extra]
  1  a,  ALL,  <Null>,  <Null>,  <Null>,  <Null>,  4673841,  ,
  2  c,  ref,  debtorNoteDebtorNoIdx,dateIdx,actionIdx,  debtorNoteDebtorNoIdx,  6,  accounts.debtorno,  40,  Using where,
  3  b,  ref,  debtorDebtorNoIdx,collectorIdx,  debtorDebtorNoIdx,  7,  debtorNotes.debtorno,  10,  Using where,
  

  
  If we try to execute the query, it's simply hung up.
  

  
  while in the same hung up state, I tried to explain the same query using another instance. This time it displays
  

  
  [Row,  table,  type,  possiblekeys,  key,  keylen,  ref,  rows,  Extra]
  1  b,  ref,  debtorDebtorNoIdx,collectorIdx,  collectorIdx,  4,  const,  4519,  Using where,
  2  c,  ref,  debtorNoteDebtorNoIdx,dateIdx,actionIdx,  debtorNoteDebtorNoIdx,  6,  debtors.debtorno,  40,  Using where,
  3  a,  ref,  accountDebtorNoIdx,  accountDebtorNoIdx,  7,  debtorNotes.debtorno,  1,  Using where,
  

  
  And this time it displays the result in seconds. (first is still running)
  

  
  My question is why the optimizer takes the order of tables differently each time
  If it runs the query in the second I'll get the result quickly.
  

  
  Is there any way to force the optimizer to follow a particular order?
  

  
  Note: All the joins in my query are inner join and the query is automatically generated from crystal.
  

  
  Thanks in advance,
  harmonycitra
  

  Report abuse