Mysql – three tables relation order is correct but values are false

aggregatejoin;MySQLperformancequery-performancerelations

i have those tables

     table1
|  id  |  name  |
|  1   |  axe   |
|  2   |  bow   |
|  3   |  car   |
|  4   |  dart  |


        table2                                        table3
|  t1_id  |  number  |                        |  t1_id  |  letter  |
|  1      |  5       |                        |  1      |  a       |
|  1      |  6       |                        |  1      |  b       |
|  1      |  2       |                        |  1      |  c       |
|  2      |  2       |                        |  2      |  a       |
|  2      |  2       |                        |  2      |  c       |
|  2      |  3       |                        |  2      |  r       |
|  3      |  8       |                        |  3      |  y       |
|  3      |  3       |                        |  3      |  i       |
|  3      |  1       |                        |  3      |  a       |
|  4      |  8       |                        |  4      |  a       |
|  4      |  9       |                        |  4      |  b       |
|  4      |  10      |                        |  4      |  c       |

and table1(id) is linked with table2(t1_id), table3(t1_id)

i run it to get them order by the highest letter_count match then by the highest average_number match to get this proper result http://www.sqlfiddle.com/#!9/69086b/8/0

SELECT 
  t1.id, 
  t1.name

FROM 
  table1 t1

INNER JOIN 
  table2 t2
    ON t2.t1_id = t1.id

LEFT JOIN 
  table3 t3
    ON t3.t1_id = t1.id
      AND t3.letter IN ('a', 'b', 'c')

GROUP BY
  t1.id

ORDER BY
  COUNT(t3.letter) DESC,
  AVG(t2.number) DESC

|  id  |  name  |
|  4   |  dart  |
|  1   |  axe   |
|  2   |  bow   |
|  3   |  car   |

and everything is working ok

but when i wanted to check if there is any problems with the query i decided to check the letter_count and avg_number so i used this query

SELECT 
  t1.id, 
  t1.name, 
  COUNT(t3.letter) AS letter_count, 
  AVG(t2.number) AS avg_number

FROM 
  table1 t1

INNER JOIN 
  table2 t2
    ON t2.t1_id = t1.id

LEFT JOIN 
  table3 t3
    ON t3.t1_id = t1.id
      AND t3.letter IN ('a', 'b', 'c')

GROUP BY
  t1.id

ORDER BY
  letter_count DESC,
  avg_number DESC

what i expected the result to be was

|  id  |  name  |  letter_count  |  avg_number   |
|  4   |  dart  |  3             |  9            |
|  1   |  axe   |  3             |  4.3333333333 |
|  2   |  bow   |  2             |  2.3333333333 |
|  3   |  car   |  1             |  4            |

but the result i got was http://www.sqlfiddle.com/#!9/69086b/3/0

|  id  |  name  |  letter_count  |  avg_number   |
|  4   |  dart  |  9             |  9            |
|  1   |  axe   |  9             |  4.3333333333 |
|  2   |  bow   |  6             |  2.3333333333 |
|  3   |  car   |  3             |  4            |

am i supposed to be worried on how the letter_count is multiplied three times on the future or does this just happens when i want to select the values and nothing bad is going to happen if i just kept it in ORDER BY?

i just want them to be ordered correctly which is what i got but checking the values showed me that letter_count is multiplied many times so i am confused about its performance or the letter_count can be ignored and the performance wont be affected?

Best Answer

It is common multiplying in multi-table source. Each record in table3 is joined to 3 (in above sample data, really it can differ, of course) records from table2.

If table3(t1_id,letter) is unique (by index) then COUNT(DISTINCT t3.letter) will help.

If not then the only solution is in calculate this COUNT() in subquery by table3 separately and join the result to table1.

Related Question