Skip to content
Advertisement

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)

JavaScript

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

JavaScript

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

JavaScript

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:

JavaScript

Count numbers of ads on a type:

JavaScript

Select subtype:

JavaScript

Advertisement

Answer

You can use this

JavaScript
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 ‘]

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement