1

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?

  • @Lennart, I don't really have a clue about how to redesign it. Because the main table has lots of columns which stores multiple foreign keys. Please have a look at my other question regarding the same topic: dba.stackexchange.com/questions/233360/… – Shahin Mar 28 at 14:36
  • @Lennart, I have a tours table which has lots of multiple foreign keys type of columns. Have a look please: imgur.com/a/V39MEDA like locations, countries, themes and etc. I must be able to filter them like SELECT * FROM tours WHERE countries LIKE '%4%' AND highlights LIKE '%14%'. But I know that using like query is not good from the performance perspective, that's why thought maybe meta table might work better. However, like query worked a lot better than meta table select. – Shahin Mar 29 at 7:06
1

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
0

Unless you are using MySQL 8, "row constructors" performs poorly. The following may help:

WHERE metakey IN (3,4)
  AND metaval IN (4,6)
  AND (metakey, metaval) IN ((3, 5), (4, 6))

Together with

INDEX(metakey, metaval, tour_id), INDEX(metaval, metakey, tour_id)

The Optimizer should pick the better index based on statistics.
I included tour_id to make each index "covering"; this gives another small performance boost.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.