Mysql. How to select Users from log table using two different text records

Question

I have a huge log table and I need to fetch some data for usage statistics. let's say we have a log table:

| user_id | action            |
| 12345   | app: IOs          |
| 12345   | app_version: 2018 |
| 12346   | app: Android      |
| 12346   | app_version: 2019 |
| 12347   | app: Windows      |
| 12347   | app_version: 2019 |

Is there a way to fetch all user ids who uses old(2018) mobile apps?

There is a way I did it but it is not efficient

SELECT 
     user_id
FROM 
    log
WHERE 
    action LIKE '%2018%'
AND 
    user_id IN (SELECT DISTINCT user_id FROM log WHERE(action LIKE '%IOs%' OR action LIKE '%Android%' ))
GROUP BY user_id

This query took about half an hour on production.

So in the end I want to have list of user ids as efficient as possible as I also will join another table to get their emails. What options do I have?

Any EXPLAIN output?

jarlh
– jarlh

2019-04-25 11:49:02 +00:00
Commented Apr 25, 2019 at 11:49 — jarlh
– jarlh, Commented Apr 25, 2019 at 11:49

Gordon Linoff · Accepted Answer · 2019-04-25 11:50:35Z

1

You can use aggregation:

SELECT l.user_id
FROM log l
WHERE l.action LIKE '%2018%' OR
      l.action LIKE '%IOs%' OR
      l.action LIKE '%Android%'
GROUP BY l.user_id
HAVING SUM(l.action LIKE '%2018%') > 0 AND       -- at least one 2018
       SUM(l.action LIKE '%2018%') <> COUNT(*);  -- at least one other

Unfortunately, the LIKE comparisons require scanning the log table. The only way around this would be to use a full text index.

You can simplify the logic to:

SELECT l.user_id
FROM log l
WHERE l.action REGEXP '2018|IOs|Android'
GROUP BY l.user_id
HAVING SUM(l.action LIKE '%2018%') > 0 AND       -- at least one 2018
       SUM(l.action LIKE '%2018%') <> COUNT(*);  -- at least one other

I'm not sure if one REGEXP is (marginally) faster than three LIKEs or not.

answered Apr 25, 2019 at 11:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vladk Over a year ago

Thank you for your input, I tried it but my initial query seems to be faster.

Yogesh Sharma · Accepted Answer · 2019-04-25 11:50:23Z

0

You can use EXISTS :

SELECT l.*
FROM log l
WHERE EXISTS (SELECT 1 FROM log l1 WHERE l1.user_id = l.user_id AND l1.action LIKE '%2018%');

answered Apr 25, 2019 at 11:50

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

1 Comment

Vladk Over a year ago

I've tried to run it, but it takes ages to run on my test system, so regardless of output it's much slower that my query. But still, thank you for input.

Nicholas Humphrey · Accepted Answer · 2019-04-25 17:29:53Z

0

Here is my solution with a LEFT JOIN. I understand that you have a big logging table so this might not be the best one. I also added a few more records for testing:

Basically I use the LEFT JOIN to move data from columns to rows so that I can simply filter with WHERE.

SQL fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=9db538e59b3d265e4e8d8559762e79d4

WITH log_table AS (
      SELECT *
      FROM (VALUES (12345, 'app: iOS'),
                   (12345, 'app_version: 2018'),
                   (12346, 'app: Android'),
                   (12346, 'app_version: 2019'),
                   (12347, 'app: Windows'),
                   (12347, 'app_version: 2019'),
                   (12348, 'app: iOS'),
                   (12348, 'app_version: 2019'),
                   (12349, 'app: Android'),
                   (12349, 'app_version: 2018'),
                   (12350, 'app: Windows'),
                   (12350, 'app_version: 2018')
           ) v(user_id, action)
)
SELECT 
    L.user_id
FROM 
    log_table AS L 
    LEFT JOIN log_table AS L2 
         ON L.user_id = L2.user_id
WHERE (L.action LIKE '%iOS%' OR L.action LIKE '%Android%') AND L2.action LIKE '%2018%'

The result: (only select those with iOS or Android and have 2018 version)

user_id
 12345
 12349

answered Apr 25, 2019 at 17:29

Nicholas Humphrey

1,2702 gold badges19 silver badges40 bronze badges

2 Comments

Vladk Over a year ago

Am I right understood that you usung WITH clause to emulate data and in my case with real data I can just use select statment with my log table? If so, then it also takes a long time to run :( Still, thank you for you help.

Nicholas Humphrey Over a year ago

@Vladk Yeah you only need the SELECT part to run, and change the name of the table. You might want to try out Gordon's answer as he is much more experienced with SQL.

Collectives™ on Stack Overflow

Mysql. How to select Users from log table using two different text records

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related