How can i optimise or any other better alternative to MySQL successive sub-query. -

i have 4 tables cat_l1, cat_l2, cat_l3, data , need find summary of cat_l1 how may cat_l2, cat_l3 , data under cat_l3.

i using subquery

select f.*, count(d.id) data_count      (select e.*, count(c.id) cat_l3_count            (select a.*, count(b.id) cat_l2_count cat_l1                 left outer join cat_l2 b                  on a.id = b.catl1_id                  group (a.id))as e            left outer join cat_l3 c            on e.id = c.catl1_id            group (e.id))as f      left outer join data d      on f.id = d.catl1_id      group (f.id) 

i new mysql , stack-overflow sorry incorrect way question this, question using above sub-query , getting expected output , database big, there better approach reach output or sub-query approach.

id | name | cat_l2_count | cat_l3_count | data_count  1  |   a1 |      2       |     4        |     8 2  |   b1 |      1       |     2        |     7 

to build schema use this:

-- phpmyadmin sql dump -- version 4.5.1 -- http://www.phpmyadmin.net -- -- host: 127.0.0.1 -- generation time: apr 07, 2017 @ 10:27 -- server version: 10.1.13-mariadb -- php version: 5.6.20  set sql_mode = "no_auto_value_on_zero"; set time_zone = "+00:00";   /*!40101 set @old_character_set_client=@@character_set_client */; /*!40101 set @old_character_set_results=@@character_set_results */; /*!40101 set @old_collation_connection=@@collation_connection */; /*!40101 set names utf8mb4 */;  -- -- database: `userdata` --  -- --------------------------------------------------------  -- -- table structure table `cat_l1` --  create table `cat_l1` (   `id` int(11) not null,   `name` varchar(25) not null ) engine=innodb default charset=latin1;  -- -- dumping data table `cat_l1` --  insert `cat_l1` (`id`, `name`) values (1, 'a1'), (2, 'b1');  -- --------------------------------------------------------  -- -- table structure table `cat_l2` --  create table `cat_l2` (   `id` int(11) not null,   `name` varchar(25) not null,   `catl1_id` int(11) not null ) engine=innodb default charset=latin1;  -- -- dumping data table `cat_l2` --  insert `cat_l2` (`id`, `name`, `catl1_id`) values (1, 'a2', 1), (2, 'b2', 1), (3, 'c2', 2);  -- --------------------------------------------------------  -- -- table structure table `cat_l3` --  create table `cat_l3` (   `id` int(11) not null,   `name` varchar(25) not null,   `catl1_id` int(11) not null,   `catl2_id` int(11) not null ) engine=innodb default charset=latin1;  -- -- dumping data table `cat_l3` --  insert `cat_l3` (`id`, `name`, `catl1_id`, `catl2_id`) values (1, 'a3', 1, 1), (2, 'b3', 1, 1), (3, 'c3', 1, 2), (4, 'd3', 1, 2), (5, 'e3', 2, 3), (6, 'f3', 2, 3);  -- --------------------------------------------------------  -- -- table structure table `data` --  create table `data` (   `id` int(11) not null,   `name` varchar(25) not null,   `catl1_id` int(11) not null,   `catl2_id` int(11) not null,   `catl3_id` int(11) not null ) engine=innodb default charset=latin1;  -- -- dumping data table `data` --  insert `data` (`id`, `name`, `catl1_id`, `catl2_id`, `catl3_id`) values (1, 'd1', 1, 1, 1), (2, 'd2', 1, 1, 2), (3, 'd3', 1, 1, 2), (4, 'd4', 1, 1, 2), (5, 'd5', 1, 2, 3), (6, 'd6', 1, 2, 4), (7, 'd7', 1, 2, 3), (8, 'd8', 1, 2, 4), (9, 'd9', 2, 3, 5), (10, 'd10', 2, 3, 5), (11, 'd11', 2, 3, 5), (12, 'd12', 2, 3, 5), (13, 'd13', 2, 3, 5), (14, 'd14', 2, 3, 6), (15, 'd15', 2, 3, 6);  -- -- indexes dumped tables --  -- -- indexes table `cat_l1` -- alter table `cat_l1`   add primary key (`id`);  -- -- indexes table `cat_l2` -- alter table `cat_l2`   add primary key (`id`);  -- -- indexes table `cat_l3` -- alter table `cat_l3`   add primary key (`id`);  -- -- indexes table `data` -- alter table `data`   add primary key (`id`);  -- -- auto_increment dumped tables --  -- -- auto_increment table `cat_l1` -- alter table `cat_l1`   modify `id` int(11) not null auto_increment, auto_increment=3; -- -- auto_increment table `cat_l2` -- alter table `cat_l2`   modify `id` int(11) not null auto_increment, auto_increment=4; -- -- auto_increment table `cat_l3` -- alter table `cat_l3`   modify `id` int(11) not null auto_increment, auto_increment=7; -- -- auto_increment table `data` -- alter table `data`   modify `id` int(11) not null auto_increment, auto_increment=16; /*!40101 set character_set_client=@old_character_set_client */; /*!40101 set character_set_results=@old_character_set_results */; /*!40101 set collation_connection=@old_collation_connection */;