Select count from multiple tables

Sometimes a parent table have more than one table with multiple references to a field in the parent table. Then the question is how to count references from more than one table in a single query. I have found 5 different ways to achieve this result, and therefore I have run a few tests, to see which was better.

To test this I made this small PHP script, that creates a parent table and child table. Then a loop populates the tables with 1,000 parent rows, approximately 15 children of type 1 and approximately 75 children of type 2. This is done before each query, to make sure that all queries have the same conditions.

1. Dependent subquery
   Execution time: 0.06 sec
2. Cross join
   Execution time: 1.01 sec
3. Derived table / Inline view
   Execution time: 0.15 sec
4. Merge view
   Creating views: 0.005 sec
   Execution time: 0.15 sec
5. Temptable view
   Creating views: 0.005 sec
   Execution time: 0.15 sec

So the conclusion is that using dependent subqueries is actually extremely fast. In this case precisely 3 times as fast as using views and 15 times faster than joining the tables and count distinct children. Notice that it takes almost no time to create the views, so it does not do any difference if the view is created inline as a derived table or as an actual view. One should expect selecting from a view to be faster, but this might be slower because the view is not indexed, and therefore joining is slower. In Oracle it is possible to create indexes on a view, even on an inline view, and therefore this method might be preferred.

By the way, a sixth way exist by first selecting from parent, and then for each parent make a new query to count each child table. Never do this, making so many queries takes much more time than using subqueries.

Here is the PHP script I used to make the tests, you can copy it to reproduce the tests. Just remember to adjust the MySQL connection string and the database name. Also make sure that you do not have a table names parents, children, children_view_1 or children_view_2, as these will be dropped on execution. Also you might prefer to execute in a command prompt to avoid timeout, event though it does not take many minutes to execute.

1. <?
2. mysql_connect("localhost", "root", "test");
3. mysql_select_db("test");
4. function test_query($q, $alg = false) {
5. for ($n = 0; $n < 3; $n++) {
6. mysql_query("DROP VIEW children_view_1");
7. mysql_query("DROP VIEW children_view_2");
8. mysql_query("DROP TABLE parents");
9. mysql_query("DROP TABLE children");
10. mysql_query("CREATE TABLE parents (
11. id int(6) not null primary key auto_increment
12. )");
13. mysql_query("CREATE TABLE children (
14. id int(6) not null primary key auto_increment,
15. parent_id int(6) not null,
16. type tinyint(1) not null,
17. index(parent_id, type),
18. index(type, parent_id)
19. )");
20. for ($i = 1; $i <= 1000; $i++) {
21. mysql_query("INSERT INTO parents(id) VALUES('".$i."')");
22. mysql_query("INSERT INTO children (parent_id, type)
23. VALUES('".$i."', '1')".str_repeat(",('".$i."', '1')", rand(10, 20)));
24. mysql_query("INSERT INTO children (parent_id, type)
25. VALUES('".$i."', '2')".str_repeat(",('".$i."', '2')", rand(50, 100)));
26. //if ($i % 100 == 0) echo ($i/10)."%\n";
27. }
28. $start = microtime(true);
29. if ($alg) {
30. mysql_query("CREATE ALGORITHM=".$alg." VIEW children_view_1 AS
31. SELECT parent_id, COUNT(*) AS n_1 FROM children
32. WHERE type = 1 GROUP BY parent_id");
33. mysql_query("CREATE ALGORITHM=".$alg." VIEW children_view_2 AS
34. SELECT parent_id, COUNT(*) AS n_2 FROM children
35. WHERE type = 2 GROUP BY parent_id");
36. echo "View: ".(microtime(true)-$start)." sec\n";
37. }
38. mysql_query($q);
39. echo mysql_error();
40. echo "Query: ".(microtime(true)-$start)." sec\n";
41. }
42. }
43. echo "SUBQUERY STYLE\n";
44. test_query("SELECT a.id,
45. (SELECT COUNT(b.id) FROM children b WHERE b.parent_id = a.id AND b.type = 1) AS n_1,
46. (SELECT COUNT(b.id) FROM children b WHERE b.parent_id = a.id AND b.type = 2) AS n_2
47. FROM parents a;");
48. echo "CROSS JOIN STYLE\n";
49. test_query("SELECT a.id,
50. COUNT(DISTINCT b.id) AS n_1,
51. COUNT(DISTINCT c.id) AS n_2
52. FROM parents a
53. LEFT JOIN children b ON(b.parent_id = a.id AND b.type = 1)
54. LEFT JOIN children c ON(c.parent_id = a.id AND c.type = 2)
55. GROUP BY a.id;");
56. echo "DERIVED TABLE/INLINE VIEW STYLE\n";
57. test_query("SELECT a.id, b.n_1, c.n_2 FROM parents a
58. LEFT JOIN (SELECT parent_id, COUNT(*) AS n_1 FROM children
59. WHERE type = 1 GROUP BY parent_id) b ON (b.parent_id = a.id)
60. LEFT JOIN (SELECT parent_id, COUNT(*) AS n_2 FROM children
61. WHERE type = 2 GROUP BY parent_id) c ON (c.parent_id = a.id);");
62. echo "MERGE VIEW STYLE\n";
63. test_query("SELECT a.id, b.n_1, c.n_2
64. FROM parents a
65. LEFT OUTER JOIN children_view_1 b ON (b.parent_id = a.id)
66. LEFT OUTER JOIN children_view_2 c ON (c.parent_id = a.id);", "MERGE");
67. echo "TEMPTABLE VIEW STYLE\n";
68. test_query("SELECT a.id, b.n_1, c.n_2
69. FROM parents a
70. LEFT OUTER JOIN children_view_1 b ON (b.parent_id = a.id)
71. LEFT OUTER JOIN children_view_2 c ON (c.parent_id = a.id);", "TEMPTABLE");
72. ?>