MySQL Forums :: Performance :: High load - queries take long time

>>>>> CONTINUATION >>>>>

mysql> show processlist;
+----------+-----------------+---------------------------+----------------------+---------+------+-----------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-----------------+---------------------------+----------------------+---------+------+-----------------------------+------------------------------------------------------------------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 1315 | Waiting for next activation | NULL |
| 48672685 | root | 192.168.0.101:49644 | database3 | Sleep | 0 | | NULL |
| 48724318 | root | www4.mydomain.com:41571 | database2 | Query | 21 | Updating | UPDATE Puntuacio SET ultim_acces = CURDATE() ,Punts = 8 WHERE UserID = 1601665643 |
| 48724344 | root | www2.mydomain.com:51938 | database2 | Query | 21 | statistics | SELECT * FROM Puntuacio WHERE UserID = 1248624805 |
| 48724368 | root | www4.mydomain.com:41693 | database2 | Query | 21 | Updating | UPDATE Puntuacio SET ultim_acces = CURDATE() ,Punts = 58 WHERE UserID = 1508088080 |
| 48724460 | root | www3.mydomain.com:46913 | database2 | Query | 21 | statistics | SELECT * FROM Puntuacio WHERE UserID = 755629639 |
| 48724517 | root | www4.mydomain.com:42054 | database2 | Query | 21 | statistics | SELECT * FROM Puntuacio WHERE UserID = 1161231365 |
| 48724523 | root | www3.mydomain.com:47100 | database2 | Query | 21 | statistics | SELECT * FROM Puntuacio WHERE UserID = 691907296 |
| 48724727 | root | www4.mydomain.com:42631 | database2 | Query | 21 | Updating | UPDATE Puntuacio SET ultim_acces = CURDATE() ,Punts = 75 WHERE UserID = 538029372 |
| 48724731 | root | www4.mydomain.com:42634 | database2 | Query | 21 | Updating | UPDATE Puntuacio SET ultim_acces = CURDATE() ,Punts = 34 WHERE UserID = 1246256888 |
| 48724746 | root | www4.mydomain.com:42676 | database3 | Query | 15 | statistics | SELECT * FROM Puntuacio WHERE UserID = 1391722519 |
| 48724806 | root | www2.mydomain.com:53218 | database2 | Query | 21 | statistics | SELECT * FROM Puntuacio WHERE UserID = 1618030351 |
| 48724824 | root | www2.mydomain.com:53264 | database2 | Query | 15 | update | INSERT INTO Puntuacio_a_Tormar(SenderID, ReceiverID, ultim_acces) VALUES(1524155886, 1313141381, CUR |
| 48724848 | root | www3.mydomain.com:47988 | database2 | Query | 15 | update | INSERT INTO Puntuacio_a_Tormar(SenderID, ReceiverID, ultim_acces) VALUES(100000009417715, 1197471982 |
| 48724979 | root | www3.mydomain.com:48227 | database2 | Query | 21 | statistics | SELECT * FROM Puntuacio WHERE UserID = 1585839660 |
| 48725040 | root | www4.mydomain.com:43448 | database2 | Query | 21 | Updating | UPDATE Puntuacio SET ultim_acces = CURDATE() ,Punts = 148 WHERE UserID = 716124098 |
| 48725161 | root | www3.mydomain.com:48756 | database3 | Query | 21 | statistics | SELECT * FROM Puntuacio WHERE UserID = 1414279364 |
| 48725269 | root | www3.mydomain.com:49077 | database2 | Query | 21 | Updating | UPDATE Puntuacio SET ultim_acces = CURDATE() ,Punts = 449 WHERE UserID = 1496077379 |
| 48725322 | root | www2.mydomain.com:54593 | database2 | Query | 21 | update | INSERT INTO Puntuacio(UserID, Punts, ultim_acces) VALUES(1110095144, 1, CURDATE() ) |
| 48725338 | root | www3.mydomain.com:49260 | database2 | Query | 21 | update | INSERT INTO Puntuacio_a_Tormar(SenderID, ReceiverID, ultim_acces) VALUES(1265974566, 100000207614710 |
| 48725417 | root | www4.mydomain.com:58312 | database2 | Query | 21 | statistics | SELECT * FROM Puntuacio WHERE UserID = 1137556053 |
| 48725671 | root | www2.mydomain.com:55546 | database2 | Query | 21 | statistics | SELECT * FROM Puntuacio WHERE UserID = 1418704634 |
| 48725755 | root | www2.mydomain.com:55775 | database2 | Query | 21 | statistics | SELECT * FROM Puntuacio WHERE UserID = 1427691604 |
| 48725834 | root | www4.mydomain.com:59338 | database2 | Query | 21 | statistics | SELECT * FROM Puntuacio WHERE UserID = 1552066581 |
| 48725940 | root | www4.mydomain.com:59791 | database2 | Query | 15 | statistics | SELECT * FROM Puntuacio WHERE UserID = 1733782214 |
| 48726097 | root | www4.mydomain.com:60269 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1391326238 |
| 48726100 | root | www4.mydomain.com:60277 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 100000131398251 |
| 48726104 | root | www4.mydomain.com:60295 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1139334992 |
| 48726106 | root | www3.mydomain.com:51651 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 100000223676017 |
| 48726111 | root | www3.mydomain.com:51700 | database2 | Query | 62 | statistics | SELECT Punts FROM Puntuacio WHERE UserID = 1382918432 |
| 48726141 | root | www3.mydomain.com:51800 | database2 | Query | 17 | updating | DELETE FROM Puntuacio_a_Tormar WHERE SenderID = 1341872568 AND ReceiverID = 1668711522 |
| 48726149 | root | www3.mydomain.com:51836 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1550372971 |
| 48726162 | root | www2.mydomain.com:57076 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1329541768 |
| 48726165 | root | www4.mydomain.com:60445 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1842152837 |
| 48726173 | root | www3.mydomain.com:51887 | database3 | Query | 67 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1473412935 |
| 48726175 | root | www2.mydomain.com:57104 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1521127706 |
| 48726179 | root | www4.mydomain.com:60466 | database3 | Query | 66 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1267350108 |
| 48726187 | root | www3.mydomain.com:51911 | database3 | Query | 67 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1024080395 |
| 48726207 | root | www4.mydomain.com:60533 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 100000436994595 |
| 48726210 | root | www4.mydomain.com:60537 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 100000060420115 |
| 48726222 | root | www4.mydomain.com:60584 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1794067217 |
| 48726224 | root | www3.mydomain.com:52012 | database3 | Query | 65 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1015868635 |
| 48726226 | root | www2.mydomain.com:57264 | database2 | Query | 94 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1393941184 |
| 48726234 | root | www4.mydomain.com:60713 | database2 | Query | 21 | Sorting result | SELECT * FROM Puntuacio_Nivells WHERE Punts > 1 ORDER BY Nivell ASC |
| 48726235 | root | www4.mydomain.com:60716 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1814894872 |
| 48726241 | root | www2.mydomain.com:57386 | database2 | Query | 67 | closing tables | INSERT Users(UserID,first_name, last_name, birthday,hometown_location_country, name,pic_big) VALUES( |
| 48726256 | root | www3.mydomain.com:52150 | database2 | Query | 15 | Sorting result | SELECT * FROM Puntuacio_Nivells WHERE Punts > 1 ORDER BY Nivell ASC |
| 48726258 | root | www3.mydomain.com:52155 | database2 | Query | 15 | statistics | SELECT Punts FROM Puntuacio WHERE UserID = 100000398267814 |
| 48726284 | root | www3.mydomain.com:52197 | database3 | Query | 66 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1024080395 |
| 48726290 | root | www3.mydomain.com:52221 | database2 | Query | 15 | statistics | SELECT Punts FROM Puntuacio WHERE UserID = 775143491 |
| 48726291 | root | www2.mydomain.com:57464 | database2 | Query | 17 | statistics | SELECT Punts FROM Puntuacio WHERE UserID = 100000382596602 |
| 48726293 | root | www2.mydomain.com:57471 | database3 | Query | 66 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1024080395 |
| 48726295 | root | www2.mydomain.com:57497 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 585232229 |
| 48726298 | root | www2.mydomain.com:57502 | database3 | Query | 15 | statistics | SELECT Punts FROM Puntuacio WHERE UserID = 1557842471 |
| 48726299 | root | www4.mydomain.com:60822 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1393941184 |
| 48726301 | root | www3.mydomain.com:52260 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1553299009 |
| 48726303 | root | www3.mydomain.com:52261 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1636316887 |
| 48726305 | root | www4.mydomain.com:60833 | database2 | Query | 13 | statistics | SELECT Punts FROM Puntuacio WHERE UserID = 1189257451 |
| 48726311 | root | www4.mydomain.com:60855 | database2 | Query | 15 | Sorting result | SELECT * FROM Puntuacio_Nivells WHERE Punts > 93 ORDER BY Nivell ASC |
| 48726312 | root | www4.mydomain.com:60856 | database2 | Query | 67 | closing tables | UPDATE Users SET ultim_access = CURDATE(), pic_big='http://profile.ak.fbcdn.net/v224/477/0/n16171455 |
| 48726317 | root | www2.mydomain.com:57554 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1449960466 |
| 48726323 | root | www4.mydomain.com:60877 | database2 | Query | 66 | freeing items | UPDATE Users SET ultim_access = CURDATE(), pic_big='http://profile.ak.fbcdn.net/v222/230/120/n627262 |
| 48726326 | root | www4.mydomain.com:60892 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1623283591 |
| 48726328 | root | www2.mydomain.com:57579 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1425957240 |
| 48726330 | root | www2.mydomain.com:57591 | database2 | Query | 66 | Updating | UPDATE Users SET ultim_access = CURDATE(), pic_big='http://profile.ak.fbcdn.net/v229/667/1/n10000025 |
| 48726332 | root | www3.mydomain.com:52322 | database2 | Query | 66 | Updating | UPDATE Users SET ultim_access = CURDATE(), pic_big='http://profile.ak.fbcdn.net/v223/22/62/n13430735 |
| 48726333 | root | www2.mydomain.com:57595 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1530545379 |
| 48726343 | root | www3.mydomain.com:52346 | database2 | Query | 66 | Updating | UPDATE Users SET ultim_access = CURDATE(), pic_big='' WHERE UserID = 1542854555 |
| 48726344 | root | www3.mydomain.com:52354 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1656581268 |
| 48726345 | root | www4.mydomain.com:60935 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 100000101262780 |
| 48726349 | root | www2.mydomain.com:57618 | database2 | Query | 66 | freeing items | UPDATE Users SET ultim_access = CURDATE(), pic_big='http://profile.ak.fbcdn.net/v222/868/119/n100000 |
| 48726350 | root | www2.mydomain.com:57625 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1201376528 |
| 48726351 | root | www3.mydomain.com:52380 | database2 | Query | 66 | update | INSERT Users(UserID,first_name, last_name, birthday,hometown_location_country, name,pic_big) VALUES( |
| 48726359 | root | www3.mydomain.com:52416 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 100000241230329 |
| 48726360 | root | www4.mydomain.com:60993 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1090142679 |
| 48726363 | root | www4.mydomain.com:32779 | database2 | Query | 67 | closing tables | INSERT Users(UserID,first_name, last_name, birthday,hometown_location_country, name,pic_big) VALUES( |
| 48726365 | root | www3.mydomain.com:52443 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 1624868327 |
| 48726366 | root | www3.mydomain.com:52449 | database2 | Query | 66 | Updating | UPDATE Users SET ultim_access = CURDATE(), pic_big='http://profile.ak.fbcdn.net/v228/3/47/n121209977 |
| 48726369 | root | www2.mydomain.com:57688 | database2 | Query | 67 | closing tables | UPDATE Users SET ultim_access = CURDATE(), pic_big='' WHERE UserID = 1395726490 |
| 48726371 | root | www2.mydomain.com:57693 | database2 | Query | 21 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 100000241230329 |
| 48726375 | root | www2.mydomain.com:57729 | database2 | Query | 15 | statistics | SELECT Punts FROM Puntuacio WHERE UserID = 1203983237 |
| 48726377 | root | www3.mydomain.com:52510 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 658741466 |
| 48726380 | root | www2.mydomain.com:57748 | database2 | Query | 66 | freeing items | INSERT Users(UserID,first_name, last_name, birthday,hometown_location_country, name,pic_big) VALUES( |
| 48726382 | root | www2.mydomain.com:57752 | database2 | Query | 70 | Sending data | SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 733607854 |
| 48726384 | root | www3.mydomain.com:52524 | database2 | Query | 66 | Updating | UPDATE Users SET ultim_access = CURDATE(), pic_big='http://profile.ak.fbcdn.net/v223/1173/51/n100000 |
| 48726385 | root | www2.mydomain.com:57753 | database2 | Query

>>>> TRUNCATED <<<<<

| 48727339 | root | www4.mydomain.com:35666 | database2 | Query | 1 | statistics | SELECT * FROM Users WHERE UserID = 1171277752 |
| 48727341 | root | www4.mydomain.com:35679 | database1 | Query | 0 | Opening tables | DELETE FROM Puntuacio_a_Tormar WHERE SenderID = 100000018346373 AND ReceiverID = 1691898903 |
| 48727343 | root | www2.mydomain.com:35394 | database1 | Sleep | 1 | | NULL |
| 48727344 | root | www3.mydomain.com:34852 | database2 | Query | 1 | statistics | SELECT * FROM Users WHERE UserID = 100000069884644 |
| 48727345 | root | www4.mydomain.com:35694 | database1 | Query | 0 | Opening tables | DELETE FROM Puntuacio_a_Tormar WHERE SenderID = 100000371076484 AND ReceiverID = 100000345789666 |
| 48727346 | root | www4.mydomain.com:35702 | database1 | Sleep | 1 | | NULL |
| 48727347 | root | www2.mydomain.com:35419 | database1 | Sleep | 1 | | NULL |
+----------+-----------------+---------------------------+----------------------+---------+------+-----------------------------+------------------------------------------------------------------------------------------------------+
472 rows in set (0.00 sec)


mysql> desc Users;
+---------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+---------------------+------+-----+---------+-------+
| UserID | bigint(20) unsigned | NO | PRI | NULL | |
| first_name | varchar(20) | NO | | NULL | |
| last_name | varchar(30) | NO | | NULL | |
| birthday | varchar(20) | NO | | NULL | |
| hometown_location_country | varchar(20) | NO | | NULL | |
| sex | varchar(6) | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
| pic_big | varchar(100) | NO | | NULL | |
| ultim_access | date | YES | | NULL | |
+---------------------------+---------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)


mysql> select count(*) from Users;
+----------+
| count(*) |
+----------+
| 6314254 |
+----------+
1 row in set (30.77 sec)


mysql> select count(*) from Puntuacio;
+----------+
| count(*) |
+----------+
| 13770171 |
+----------+
1 row in set (14.30 sec)


mysql> desc Puntuacio;
+-------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| UserID | bigint(20) | NO | PRI | NULL | |
| Punts | bigint(20) | NO | MUL | NULL | |
| ultim_acces | date | NO | | NULL | |
+-------------+------------+------+-----+---------+-------+
3 rows in set (4.71 sec)


mysql> explain SELECT * FROM Puntuacio WHERE UserID = 1161231365;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | Puntuacio | const | PRIMARY | PRIMARY | 8 | const | 1 | |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (4.42 sec)






Thanks!!!! Any help will be appreciated! If you need more info please ask me!