Get the count of other users having the x tag with order_id in order_id of users having the y tag

table: MyCustomTable (200K rows)

  • order_id
  • user_id
  • type ('x' and 'y') my custom tags

and the query:

SELECT   COUNT(order_id),user_id
FROM     MyCustomTable
WHERE    type='x'
AND      order_id IN (SELECT order_id
                      FROM   MyCustomTable
                      WHERE  type   ='y'
                      AND    user_id='56')
AND      user_id <> '56'
GROUP BY user_id

Create table output

CREATE TABLE `MyCustomTable` (
 `order_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `type` enum('x','y') NOT NULL DEFAULT 'x',
 PRIMARY KEY (`id`),
 KEY `customeKey` (`user_id`,`type`,`order_id`),
  • 1
    Edit the question and add the output of SHOW CREATE TABLE MyCustomTable; – ypercubeᵀᴹ May 13 '16 at 14:58
  • 1
    You are talking about counting users but your query appears to be counting orders per user. And it looks like it's the orders that are tagged, not the users. Can you make this part a little clearer? – Andriy M May 13 '16 at 15:15
  • Use EXPLAIN to get the query execution plan and add that to your question. – jkavalik May 14 '16 at 10:26

One thing to try with IN is to change it to EXISTS (with the proper modifications to the subquery):

SELECT   COUNT(order_id), user_id
FROM     MyCustomTable a
WHERE    type='x'
                      FROM   MyCustomTable b
                      WHERE  type   ='y'
                      AND    user_id='56'
                      AND    a.order_id = b.order_id)
AND      user_id <> '56'
GROUP BY user_id

Another one is to get rid of the subquery:

SELECT   COUNT(distinct a.order_id), a.user_id
FROM     MyCustomTable a
JOIN     MyCustomTable b
             ON a.order_id = b.order_id
WHERE    a.type='x'
AND      b.type='y'
AND      a.user_id <> '56'
AND      b.user_id = '56'
GROUP BY a.user_id

Test them and use EXPLAIN to check the plans used to execute them. Your question does not show indexes in that table, for fast execution some indexes will be needed - a multicolumn index on (type, user_id, order_id) covers both the main query and the subquery for the first case (and the same for your original query with IN) and works for the second case too.

| improve this answer | |
SELECT COUNT(UserID) as CountUsers
    A.user_id as UserIDs, 
    A.order_id as XTag,
    B.order_id as YTag
FROM     MyCustomTable A
INNER JOIN MyCustomTable B 
    ON A.User_ID = B.User_ID 
    AND A.Order_ID <> B.Order_ID
WHERE A.type='x' 
    AND b.type = 'y'
    AND user_id <> '56') A

Would this work?

| improve this answer | |
  • 1
    Please add an explanation as to how your answer helps the OP and answers their question. This will ensure future users benefit from your answer! – Adam Copley May 13 '16 at 17:09

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.