How to get the data of two columns and count the number?

Question

MySQL

Good afternoon, dear colleagues

At first glance, there is a simple task, but I can’t think of an optimal way to solve it. I would be glad for any help.

The task is to get all the unique rows from two columns, and count how many times each of the values ​​\u200b\u200bcame across in any of the same columns + date limit The

request that I got

SELECT 
  t.str,
  (SELECT COUNT(*) FROM tableName WHERE date BETWEEN '2021-01-01' AND '2021-12-31' AND str1 = t.str OR str2 = t.str) as count
FROM (
  (SELECT DISTINCT str1 as str FROM tableName WHERE date BETWEEN '2021-01-01' AND '2021-12-31')
  UNION
  (SELECT DISTINCT str2 as str FROM tableName WHERE date BETWEEN '2021-01-01' AND '2021-12-31')
) as t

The request works, but it would be desirable to tighten productivity. Now the request processes about 700k in 15 seconds. How can you optimize and improve the performance of this query? Indexes are worth

Thank you

Answer 1

SELECT 
  t.str,
  SUM(n)
FROM (
  (SELECT str1 as str, COUNT(*) as n FROM tableName WHERE date BETWEEN '2021-01-01' AND '2021-12-31' GROUP BY str1)
  UNION
  (SELECT str2 as str, COUNT(*) as n FROM tableName WHERE date BETWEEN '2021-01-01' AND '2021-12-31' GROUP BY str2)
) as t
 GROUP BY t.str