The question is more about query optimization. The fact is that the user can add a different type of publication (news, articles, notes, photos). All this is stored according to different tables.

What would be the optimal request for counting each type of publication?

UNION ALL not appropriate, as the tables have a different number of columns.

I did so, I do not know how fast the option is:

 SELECT ( SELECT IF(`Count` IS NULL, 0, `Count`) FROM likes WHERE IdNote = 600 ) AS likes, ( SELECT COUNT( * ) FROM commenttousers WHERE CommentToUsersIdToUser =600 ) AS reviews FROM likes, commenttousers GROUP BY CommentToUsersIdToUser LIMIT 0 , 30

In my example, the IF condition does not work for some reason.

  • @Vasile, as an option to store this data in the cache (any key-value storage) and increment after each addition to the database. - romeo
  • @Vasile, If you are given a comprehensive answer, mark it as correct (click on the check mark next to the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

In response to a query about the number of rows of the desired type there will be the same number of columns, and add it through the union.

 select sum(AB) from (select count(*) as B from tab1 union all select count(*) as B from tab2) as A;