mysql optimisation number of ads in a category

I have a personal ads website .

First problem: I have 2 tables, category and ads.

Category table (id is the primary key)

| id  | type        | subtype   | pos    |
+-----+-------------+-----------+--------+
| 1   | sell        |           | 1      |
| 2   | jobs        |           | 2      |
| 3   | dating      |           | 3      |
| 4   | dating      | boys      | 1      |
| 5   | dating      | girls     | 2      |
| 6   | sell        | cars      | 1      |
| 7   | jobs        | teacher   | 1      |

Ads table (id is the primary key) – this table has a lot of ads

| id  | title       | type      | subtype|
+-----+-------------+-----------+--------+
| 1   | some text   | sell      | cars   |
| 2   | some text   | dating    | girls  |
| 3   | some text   | dating    | boys   |

I want to read all category types and get the total number of ads for that type, and I need to get the subtypes too.

The output on my page will be something

Sell (1 ads)
   Cars

Jobs (0 ads)
   Teacher

Dating (2 ads)
  Boys
  Girls

What I am currently doing is making 3 queries using php and mysql, the first of which returns the categories type is a select where subtype=”” , the second counts existing ads in those categories and the third gives me subtype for each type, I want to optimize mysql, reduce the number of queries and be faster even if it means changing the tables and adding more indexes

Select query:

select * 
from category  
where subtype = '' 
order by pos desc

Count numbers of ads on a type:

select type 
from ads 
where type = '$type_name'

Select subtype:

select * 
from category  
where type = '$type_name' 
  and subtype !='' 
order by pos

Answer

You can use this

SELECT c.*,a.c_ads
FROM
category c LEFT JOIN ( SELECT COUNt(type) c_ads,type FROM ads GROUP BY type) a
ON c.type = a.type
ORDER BY c.type,c.pos DESC

id | type   | subtype | pos | c_ads
-: | :----- | :------ | --: | ----:
 3 | dating |         |   3 |     2
 5 | dating | girls   |   2 |     2
 4 | dating | boys    |   1 |     2
 2 | jobs   |         |   2 |  null
 5 | jobs   | teacher |   1 |  null
 1 | sell   |         |   1 |     1
 5 | sell   | cars    |   1 |     1

see fiddle

When you get the data in php check row by row, if the type changes and then you can can use the column c_ads to Display ($row{‘c_ads’] ads) and row[‘type’] to display the text of the category

Where row[‘type’] doesn’t change you find the subcategories to row[‘type’] in row[‘subtype ‘]