What are these queries doing?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

What are these queries doing?

Post by JayBird »

My site went down today and my host said it was becuase some up the queries were lasting upwards of 250 seconds without closing.

the top 5 queries that were taking ages were

Code: Select all

Kill   1   mbeech   localhost.localdomain:47115   themgzr_co_uk_-_newforum   Query   238   Copying to tmp table   SELECT f . * , p . * , pt . * , t . * , u . * 
FROM phpbb_topics AS t, phpbb_users AS u, phpbb_post  
 Kill   2   mbeech   localhost.localdomain:47117   themgzr_co_uk_-_newforum   Query   239   Copying to tmp table   SELECT f . * , p . * , pt . * , t . * , u . * 
FROM phpbb_topics AS t, phpbb_users AS u, phpbb_post  
 Kill   3   mbeech   localhost.localdomain:47126   themgzr_co_uk_-_newforum   Query   155   Locked   SELECT aa.forum_id, u.user_id, u.username
FROM phpbb_auth_access aa, phpbb_user_group ug, phpbb_g  
 Kill   4   mbeech   localhost.localdomain:47135   themgzr_co_uk_-_newforum   Query   232   Locked   UPDATE phpbb_users SET user_session_time =1114161993,
user_session_page =0,
user_lastvisit =1  
 Kill   5   mbeech   localhost.localdomain:47140   themgzr_co_uk_-_newforum   Query   228   Locked   SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = '26425b9ff7ee55b3f0f
Im not really sure what these queries are doing, especially the top two (copy to temp table!?!?). Can someone enlighten me?

Thanks

Mark
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post by phpScott »

I found a couple of articles that talk about the tmp table issue and what might be causing it.

http://wordpress.org/support/topic.php?id=27925
http://weblog.burningbird.net/archives/ ... tmp-table/

the mysql documentation says:
Creating tmp table

The thread is creating a temporary table to hold a part of the result for the query.
If you have access to phpMyAdmin on your site and have the right privilages you can also use the analyze and optimize functions within it or run the correct query yourself

hope that helps a little

It is the last two process that are concerning to me, but then I don't know what you are doing with the sessions in your db and whether those are your scripts or not.

phpscott
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Cheerz for that mate
Post Reply