0

I have the next query:

SELECT
    parent1.name,
    parent2.name
FROM myTable AS child, myTable AS parent1, myTable AS parent2
WHERE
    child.parentId = parent1.id AND
    parent1.parentId = parent2.id;

I put the same in from block 3 times. Does it means that MySQL will scan the table 3 times?

Enbugger
  • 314
  • 2
  • 6
  • 23
  • 1
    Your language is not entirely clear. MySQL will do two self-joins of the `myTable` table. Whether it scans, seeks, or something else might depend on the query plan. – Tim Biegeleisen Jul 12 '17 at 07:08
  • Give this a thorough read: https://dev.mysql.com/doc/refman/5.5/en/using-explain.html - it talks about how to get MySQL to tell you how it will execute a query. – GregHNZ Jul 12 '17 at 07:41
  • Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) – Madhur Bhaiya Nov 01 '18 at 19:57
  • 1
    Optimizers in RDBMS generally would ensure that there aren't multiple scans in this case. Indexing would even speed up further! But, for confirming this, you can analyze `Explain` statement result on this query. – Madhur Bhaiya Nov 01 '18 at 19:59

0 Answers0