Mysql – Count results after filtering out

countMySQLmysql-5.7

I am using MySQL 5.7 and need to count results after filtering out the data.
My table structure:

+----+---------+---------+---------+
| id | metakey | metaval | tour_id |
+----+---------+---------+---------+
|  1 |       4 |      15 |       1 |
|  2 |       4 |      18 |       1 |
|  3 |       4 |      23 |       1 |
|  4 |       4 |      19 |       2 |
|  5 |       4 |      23 |       2 |
|  6 |       4 |      25 |       2 |
|  7 |       4 |      27 |       3 |
|  8 |       4 |      29 |       3 |
|  9 |       3 |       5 |       1 |
| 10 |       2 |       6 |       1 |
| 11 |       3 |       5 |       2 |
| 12 |       2 |       6 |       2 |
+----+---------+---------+---------+
  1. metakey -> is something predefined, like 4 for countries,
  2. metaval -> is real data id, for example country id

Let's say I selected rows by the following query:

SELECT tour_id 
from metas 
WHERE (metakey, metaval) IN ((3, 5), (2, 6)) 
group by tour_id 
having COUNT(tour_id) = 2;

+---------+
| tour_id |
+---------+
|       1 |
|       2 |
+---------+

Now I need to extract a number of tours for each country (country meta key is 4) of that resultset.

Expected result: 

|-----------|---------|
|country_id |  count  |
|-----------|---------|
|    15     |   1     |
|-----------|---------|
|    18     |   1     |
|-----------|---------|
|    19     |   1     |
|-----------|---------|
|    23     |   2     |
|-----------|---------|
|    25     |   1     |
|-----------|---------|

I wrote a query which returns a correct resultset, but it took 0.0231 seconds. Number of rows in table: 35036

SELECT metaval, count(tour_id) as numberOfTours
from metas2
where tour_id in ( SELECT tour_id
                   from metas2
                   WHERE (metakey, metaval) IN ((3, 5), (2, 6))
                   group by tour_id
                   having COUNT(tour_id) = 2 ) 
      AND metakey = 4
GROUP BY metaval

+---------+---------------+
| metaval | numberOfTours |
+---------+---------------+
|      15 |             1 |
|      18 |             1 |
|      19 |             1 |
|      23 |             2 |
|      25 |             1 |
+---------+---------------+
5 rows in set (0.00 sec)

Does anyone have tips to improve performance?

Best Answer

Test:

SELECT metaval, COUNT(DISTINCT tour_id) 
FROM metas, ( SELECT tour_id 
              FROM metas 
              WHERE (metakey, metaval) IN ((3, 5), (4, 6)) -- or (2, 6) ?
              GROUP BY tour_id 
              HAVING COUNT(tour_id) = 2 ) tours 
WHERE metas.tour_id = tours.tour_id
GROUP BY metaval
Related Question