JOIN queries vs multiple queries

by

For inner joins, a single query makes sense, since you only get matching rows.
For left joins, multiple queries is much better… look at the following benchmark I did:

  1. Single query with 5 Joins

    query: 8.074508 seconds

    result size: 2268000

  2. 5 queries in a row

    combined query time: 0.00262 seconds

    result size: 165 (6 + 50 + 7 + 12 + 90)

.

Note that we get the same results in both cases (6 x 50 x 7 x 12 x 90 = 2268000)

left joins use exponentially more memory with redundant data.

The memory limit might not be as bad if you only do a join of two tables, but generally three or more and it becomes worth different queries.

As a side note, my MySQL server is right beside my application server… so connection time is negligible. If your connection time is in the seconds, then maybe there is a benefit

Frank

More Related Contents:

  1. 1052: Column ‘id’ in field list is ambiguous
  2. Laravel join with 3 Tables
  3. mySQL – Create a New Table Using Data and Columns from Three Tables
  4. How to store a one to many relation in MySQL database?
  5. MySQL: Quick breakdown of the types of joins [duplicate]
  6. Reorder / reset auto increment primary key
  7. Required to join 2 tables with their FKs in a 3rd table
  8. MySQL Trigger after update only if row has changed
  9. Why does MySQL report a syntax error on FULL OUTER JOIN?
  10. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  11. Mixing ANSI 1992 JOINs and COMMAs in a query
  12. Difference Between Schema / Database in MySQL
  13. How do I add a custom CHECK constraint on a MySQL table?
  14. MySql Count cannot show 0 values
  15. MySQL JOIN with LIMIT 1 on joined table
  16. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  17. Implementing a hierarchical data structure in a database
  18. Does MySQL allows to create database with dot?
  19. Commit data in a mysql container
  20. MySQL Insert & Joins
  21. How to join two tables using a comma-separated-list in the join field
  22. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  23. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  24. Where does MySQL store database files on Windows and what are the names of the files?
  25. Should I normalize my DB or not?
  26. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  27. MySQL: Can I do a left join and pull only one row from the join table?
  28. In what order are MySQL JOINs evaluated?
  29. Inventory management with stock options
  30. Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user ‘root’@’localhost'(using password:YES)

Leave a Comment