Copying to tmp table.... FOREVER

[jetsettt]

Hi all,

 

Its not often I ask for help but this issue has got me. I have a query that lists all users in the DB, when they last signed in and when a signin reminder was last sent. The query runs fine on the test server with 60 users but will not run on the live server with 2500 users in the DB. The status of the query stays at 'Copying to tmp table' and it does not complete. The query syntax looks good.

 

MySQL version: 5.0.75

query_cache_size = 16M

query_cache_limit=1M

 

 

2 Tables used are 'Users'  and  'Log01'

 

My log types are '3 =  Last time the user signed-in'  and '12 = Last time an email reminder was sent'

 

 

 

SELECT
Max(last_time.log_time) AS last_signed_in,
Users.User_email,
Max(reminder.log_time) AS last_reminder_sent
FROM
Users
Left Join log01 AS last_time ON last_time.log_usr = Users.User_id AND last_time.log_type = 3
Left Join log01 AS reminder ON reminder.log_usr = Users.User_id AND reminder.log_type = 12

 

 

Thnaks to anyone who can help me

[Andy-H]

Do you mean MySQL Views?

[jetsettt]

Don't know how that will address the issue?

 

My problem seems to be related when table 'log01' is added to the query as 'reminder.log_time'. I f I remove 'Left Join log01 AS reminder ON reminder.log_usr = Users.User_id AND reminder.log_type = 12' the query will run but I won't have the full result set that I require. I have also now noticed that after a while as 'Copying to tmp table' is still running, all tables being accessed on the server start to lock?

 

[awjudd]

Something like this should work:

 

SELECT u.User_email, lt.log_time AS last_signed_in
FROM Users u
JOIN ( SELECT User_id, MAX(log_time) AS last_time FROM log01 WHERE log_type = 3 GROUP BY User_id ) lt ON u.User_id = lt.User_id
JOIN ( SELECT User_id, MAX(log_time) AS last_time FROM log01 WHERE log_type = 12 GROUP BY User_id ) r ON u.User_id = r.User_id

 

Basically the problem is you are having is that effectively making a CROSS JOIN for the log table which is going to be bad ...

 

Another approach is:

SELECT Users.User_email
          , MAX(CASE WHERE lt.log_type = 3 THEN lt.log_time ELSE NULL END) AS last_signed_in
          , MAX(CASE WHERE lt.log_type = 12 THEN lt.log_time ELSE NULL END) AS last_reminder_sent
FROM Users u
JOIN log01 AS lt ON lt.log_usr = Users.User_id
WHERE l.log_type IN ( 3, 12 )

 

Basically the first query will go against the log table and find the maximum log time for each user for the reminders and log in times and then just use that in the outside.  This will be able to run quicker than yours is currently because each table will be returning up to 1 row per user.

 

The second one which I think is a bit cleaner but could be slower.  It is essentially just grabbing all of the rows where the log type is either 3 or 12 and then processing it in the MAX function (determining what to send) based on the log type.

 

Does this make sense?

 

~juddster

[jetsettt]

Thnaks for your reply and help Juddster.

 

I have tried both queries and neither work. It may be that I don't understand how they are querying the tables as the commands are new to me.

 

I have altered 

SELECT u.User_email, lt.log_time AS last_signed_in
FROM Users u
JOIN ( SELECT User_id, MAX(log_time) AS last_time FROM log01 WHERE log_type = 3 GROUP BY User_id ) lt ON u.User_id = lt.User_id
JOIN ( SELECT User_id, MAX(log_time) AS last_time FROM log01 WHERE log_type = 12 GROUP BY User_id ) r ON u.User_id = r.User_id

 

to...

 

SELECT Users.User_email, Log01.log_time AS last_signed_in
FROM Users, log01
JOIN ( SELECT User_id, MAX(log_time) AS last_time FROM log01 WHERE log_type = 3 GROUP BY User_id ) lt ON Users.User_id = log01.User_id
JOIN ( SELECT User_id, MAX(log_time) AS last_time FROM log01 WHERE log_type = 12 GROUP BY User_id ) r ON Users.User_id = log01.User_id

 

 

It does not work? I can't see what is wrong.

[awjudd]

The query I provided and the one that you changed it to are two completely different queries.  The one you changed it to does an ANSI JOIN without any conditions making it a CROSS JOIN.

 

What part of my queries didn't work?  Did they take too long to execute or what?

 

I noticed a typo in my query:

 

SELECT u.User_email, lt.last_time AS last_signed_in, r.last_time AS reminder
FROM Users u
LEFT JOIN ( SELECT User_id, MAX(log_time) AS last_time FROM log01 WHERE log_type = 3 GROUP BY User_id ) lt ON u.User_id = lt.User_id
LEFT JOIN ( SELECT User_id, MAX(log_time) AS last_time FROM log01 WHERE log_type = 12 GROUP BY User_id ) r ON u.User_id = r.User_id 

 

~juddster

[jetsettt]

Thanks for that, it works fantastic. The error has gone and this executes quickly.

 

SELECT u.User_email, lt.last_time AS last_signed_in, r.last_time AS reminder
FROM Users u
LEFT JOIN ( SELECT User_id, MAX(log_time) AS last_time FROM log01 WHERE log_type = 3 GROUP BY User_id ) lt ON u.User_id = lt.User_id
LEFT JOIN ( SELECT User_id, MAX(log_time) AS last_time FROM log01 WHERE log_type = 12 GROUP BY User_id ) r ON u.User_id = r.User_id 

 

 

I had to change 'User_id' to 'log_usr' to get it to work.

 

SELECT u.User_email, lt.last_time AS last_signed_in, r.last_time AS reminder
FROM Users u
LEFT JOIN ( SELECT log_usr, MAX(log_time) AS last_time FROM log01 WHERE log_type = 3 GROUP BY log_usr ) lt ON u.User_id = lt.log_usr
LEFT JOIN ( SELECT log_usr, MAX(log_time) AS last_time FROM log01 WHERE log_type = 12 GROUP BY log_usr ) r ON u.User_id = r.log_usr 

 

I will go and study this now to understand how it works.

 

Thanks very much juddster !

 

[awjudd]

Oops, sorry about that user typo   Please mark this topic as complete

 

~juddster