0

I currently have an SQL query that is taking a LONG time to load, wondered if anyone could help me? I'm fairly new with MySQL.

This is the query:

SELECT applicationid 
FROM logs 
WHERE action = 'VIEWED' 
AND userid = '$user' 
AND date >= '$dateminus7' 
AND applicationid NOT IN (
     SELECT applicationid 
     FROM logs 
     WHERE userid = '$user' 
     AND date >= '$dateminus7' 
     AND (action LIKE 'SUBMITTED NOTE%' OR action LIKE 'CHANGED STATUS%')
)

Basically looking through some databases to find users that are not leaving notes when they access customer accounts (very naughty). There are around 30,000 records per week in the logs database, which is obviously a factor, but right now the query runs for an hour and still doesn't complete (times out, 404 error on PHP page).

Any info needed just ask, I'd appreciate any tips or pointers.

EDIT: EXPLAIN results https://i.sstatic.net/xYhPt.png

Improve this question
7
  • 1
    have you added indexes on your table Commented Apr 29, 2013 at 15:31
  • 2
    We need to see an EXPLAIN statement. Commented Apr 29, 2013 at 15:41
  • Forgive me, what is an EXPLAIN statement? Commented Apr 29, 2013 at 15:53
  • EXPLAIN SELECT applicationid ... displays how the SQL engine will process the query, particularly which indexes will be used. Commented Apr 29, 2013 at 15:58
  • Are you sure this query actually works? It doesn't seem to make a lot of sense to include and exclude logs based on particular action values without looking at the sequence of those events. Commented Apr 29, 2013 at 16:04

3 Answers 3

Reset to default
1

A composite index on (userid, date) should be sufficient to speed up both queries here. You can also try (userid, date, action) or (action, userid, date). Depending on which column has more unique values, one index might be more effective than another.

Note that the query planner might not be able to optimize the subquery, and it will potentially execute the subquery once for each candidate row in the outer query. The cost of running the inner query multiple times could be contributing to the performance problem. Consider trying a join instead, and see if you get better performance:

SELECT la.applicationid 
FROM logs la

LEFT OUTER JOIN logs lb
    ON la.applicationid = lb.applicationid
    AND lb.userid = '$user' 
    AND lb.date >= '$dateminus7' 
    AND (lb.action LIKE 'SUBMITTED NOTE%' OR lb.action LIKE 'CHANGED STATUS%')

WHERE la.action = 'VIEWED' 
AND la.userid = '$user' 
AND la.date >= '$dateminus7' 
AND lb.applicationid IS NULL;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This is near enough identical to user Barmar's answer, so it sounds promising, but as I said to him, no results are ever returned, only an SQL error: 'unknown table status: TABLE_TYPE'. My 'date' field is of type 'date', is this an issue? I'm beginning to think it might be as I see results outside of the date values I expect to get back (when using my original statement in the OP).
That error seems to be a phpmyadmin bug. See stackoverflow.com/questions/2955097/…
0

I've found that MySQL doesn't optimize IN and NOT IN queries very well. Replacing them with a JOIN usually improves things. For NOT IN you have to use a LEFT OUTER JOIN:

SELECT l1.applicationid
FROM logs l1
LEFT OUTER JOIN (SELECT applicationid 
     FROM logs 
     WHERE userid = '$user' 
     AND date >= '$dateminus7' 
     AND (action LIKE 'SUBMITTED NOTE%' OR action LIKE 'CHANGED STATUS%')) l2
ON l1.applicationid = l2.applicationid
WHERE l1.action = 'VIEWED' 
AND l1.userid = '$user' 
AND l1.date >= '$dateminus7'
AND l2.applicationid IS NULL
Improve this answer

3 Comments

This is returning no results, ever, no matter what date I put into it, but it does seem to complete quickly. A question, my 'date' field is of type 'date', is this an issue? I'm beginning to think it might be as I see results outside of the date values I expect to get back.
Have you tested your query with a small test table to ensure it works? I think my query is equivalent to yours. Since your query never completes, how do you know it returns results? Can you post a small sample of data at sqlfiddle.com?
It does return results, it just takes a very, very long time, far more than is practical. Eventually the query times out, but not before (on my php page) a number of results have appeared for the first half of users.
0

You have two "action='XXX'" in two querys(main one and the subquery) and they dont ever have any records in Common I think. In other words, if you are looking for some applications with the action "VIEWED". You dont need to use the query:

SELECT applicationid FROM logs WHERE userid = '$user' AND date >= '$dateminus7' AND (action LIKE 'SUBMITTED NOTE%' OR action LIKE 'CHANGED STATUS%')

Not sure if I missed something and whether this will help. I will follow this.

Improve this answer

4 Comments

I first need to find every applicationid that a user has 'VIEWED', then I need to see if that applicationid is in the subquery. The subquery is checking if that same user left a note (the two other action conditions appear when a user leaves a note). If the applicationid cannot be found in the subquery, no note was left and that's the information I need.
@user2332653 Thx for clearfying. I think I got what you want to do. But what confused me is that do you mean it is possible for one applicationid in the table logs to have two actions at the same time? like "VIEWED" and also "'SUBMITTED NOTE%'"
Yes, but they would appear as separate entries (seperate user actions logged) in the database. When a user views some information, it is logged, when they make a note of why they viewed that information, it is logged, what I am basically doing is trying to make sure that users who view applications are also logging why they viewed it.
Ah. I got it. I dont know what the separate entries look like. But I think what you can do is tring to process the entries in one query, so you dont need to use the subquery. This may enhance the performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.