Hello!

Methodological question from the category of how to do it.

There is a bulletin board selling cars that has 230 makes and 1,500 car models.

Total about 300 cities where there are ads.

Thus, there are 4 tables: brands, models, cities, ads.

When a user visits a city page, for example, Khabarovsk, he sees a list of car brands and the calculated number of ads for each brand for that city.

Now this number of ads is calculated through the count in the table with ads. Each time a separate request when accessing the page.

Since there are a lot of cities, and the table with ads will become big and I have a question:

How is it better to organize the count of the number of ads for brands and models for each city ​​in this case?

  • and what does not suit you the option select count(*) from объявления where город_id = 8 and марк_id = 7 and модел_id = 6 ? - JVic
  • @Victor makes / models are counted through having count. The request takes 1.5 seconds. - Pavel
  • it would not be bad to give an example of a request - JVic
  • @Victor select tbl.title, (SELECT COUNT(*) FROM adds as a WHERE tbl.id = a.id_vendor) as count_vendor FROM vendors as tbl HAVING count_vendor > 0 - Pavel
  • try so select TBL.TITLE, count(A.id) as count_vendor from VENDORS as TBL left join ADDS as A on A.ID_VENDOR = TBL.ID group by TBL.TITLE having count(A.id) > 0 in this idea If you don’t even need a chewing, now I don’t think it is so or not! - JVic

1 answer 1

if you do not want to make a “cumbersome” request for sampling the number of ads each time, you can create another table in which the number of ads for each city, brand, model or whatever is needed will be stored. Add a trigger to insert and delete in the table объявления , which, respectively, when inserting or deleting will increase (decrease) the count of records in the table кол-во объявлений