a count for each join – optimisation

by

For optimization purposes, a good rule of thumb is to join less, not more. In fact, you should try to join as few rows as you can with as few rows as you can. With any additional join, you will multiply cost instead of adding cost. Because mysql will basically just generate a big multiplied matrix. A lot of that gets optimized away by indexes and other stuff though.

But to answer your question: it is actually possible to count with only one big join, assuming the tables have unique keys and idalb is a unique key for album. Then, and only then, you can do it similar to your code:

select alb.titreAlb as "Titre",
       count(distinct payalb.idAlb, payalb.PrimaryKeyFields) "Pays",
       count(distinct peralb.idAlb, peralb.PrimaryKeyFields) "Personnages",
       count(distinct juralb.idAlb, juralb.PrimaryKeyFields) "Jurons"
from album alb
left join pays_album payalb using ( idAlb )
left join pers_album peralb using ( idAlb )
left join juron_album juralb using ( idAlb )
where alb.titreAlb = "LES CIGARES DU PHARAON"
group by alb.titreAlb

where PrimaryKeyFields stands for the primary key fields of the joined tables (you have to look them up).

Distinct will remove the effect the other joins have on the count. But unfortunately, in general, distinct will not remove the effect the joins have on the cost.

Although, if you have indexes that cover all (idAlb + PrimaryKeyFields)-fields of your tables, that might be even as fast as the original solution (because it can optimize the distinct to not do a sorting) and will come close to what you were thinking of (just walking through every table/index once). But in a normal or worst case szenario, it should perform worse than a reasonable solution (like SlimGhost’s one) – because it is doubtful it will find the optimal strategy. But play around with it and check the explains (and post the findings), maybe mysql will do something crazy.

More Related Contents:

  1. left join query keep a column empty
  2. How can I select data from different tables?
  3. MYSQL OR vs IN performance
  4. MySQL: Quick breakdown of the types of joins [duplicate]
  5. Join between tables in two different databases?
  6. Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)
  7. MySQL JOIN ON vs USING?
  8. Storing JSON in database vs. having a new column for each key
  9. How to use mysql JOIN without ON condition?
  10. Does adding ‘LIMIT 1’ to MySQL queries make them faster when you know there will only be 1 result?
  11. How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?
  12. Speeding up row counting in MySQL
  13. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  14. MySQL JOIN the most recent row only?
  15. mysql query show multiple tables from one ID column
  16. LEFT JOIN only first row
  17. MySQL Inner Join Query Multiple Tables
  18. Difference between these two joining table approaches?
  19. Laravel join with 3 Tables
  20. MySQL Insert & Joins
  21. How to join two tables using a comma-separated-list in the join field
  22. MySQL LEFT JOIN 3 tables
  23. Using index, using temporary, using filesort – how to fix this?
  24. What is the default MySQL JOIN behaviour, INNER or OUTER?
  25. MySQL Join Where Not Exists
  26. how to use a like with a join in sql?
  27. In what order are MySQL JOINs evaluated?
  28. How to store a one to many relation in MySQL database?
  29. MySQL cartesian product between two SELECT statements
  30. MySQL Multiple Joins in one query?

Leave a Comment