0

I have this query:

UPDATE invitations SET added=added+1 WHERE chat_id NOT IN (SELECT * FROM (SELECT LEFT(name,14) AS chat_id FROM rules WHERE name LIKE '-%-credit' AND value< 1522744396) AS iner)

every time I execute it it takes like ages and it never does what i want it to do. for example I have run it now and it has taken 1600 seconds so far and nothing happened yet. In show proccesslist I see that it's in the State of "Sending data". What's wrong with this query?

The table rules has at most 100K rows and the inner query returns like 2K rows. the results of the inner query are one column and are like: chat_id -1001167043635 -1001167043643 ...

when I run the inner query alone, it takes like 0.007 seconds.

And the invitations table has 500K rows and are like: user_id | chat_id | added 55545323 | -1001167043635 | 23

it would be appreciated if you tell me what i have done wrong and tell me how to fix it.

Mohammad Amin Ghaseminia
  • 135
  • 1
  • 9

2 Answers2

0

Why do you run a "Select *"? Does the inner Select not fullfill your request?

Have you set an index on chat_id?

tom
  • 84
  • 5
  • I was just testing and i forgot to remove that extra select * , the same problem happens even without that. – Mohammad Amin Ghaseminia Apr 10 '18 at 10:14
  • I haven't added an index on chat id, because there might be repeatitive values and it's not unique. but there a unique property on both user_id and chat_id – Mohammad Amin Ghaseminia Apr 10 '18 at 10:16
  • an index just helps mysql to know the data in the column, it hasn't to be uniqe. see: https://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-in-mysql – tom Apr 10 '18 at 11:52
0

You might think that the subquery in NOT IN executes only once for the entire query. The thing is that the for every row in OUTER QUERY, the INNER QUERY will be executed once. We faced this issue once. Check with mysql EXPLAIN query.

Praveen E
  • 926
  • 8
  • 14