Re: MySQL 5.5 Queries occasionally extremely slow
Posted by:
Marc Host
Date: March 04, 2014 12:43PM
Thanks for the reply.
Here is the EXPLAIN for that query:
+----+--------------------+----------------------+-----------------+-------------------------------------+-----------+---------+--------------------------------------------------+---------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------------------+-----------------+-------------------------------------+-----------+---------+--------------------------------------------------+---------+---------------------------------------------+
| 1 | PRIMARY | listmessage | ref | messageid | messageid | 4 | const | 2 | Using index; Using temporary |
| 1 | PRIMARY | listuser | index | PRIMARY | PRIMARY | 6 | NULL | 1481922 | Using where; Using index; Using join buffer |
| 1 | PRIMARY | user | eq_ref | PRIMARY | PRIMARY | 4 | phplistdb.listuser.userid | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | phplist_user_user | eq_ref | PRIMARY,email,confirmed,blacklisted | PRIMARY | 4 | func | 1 | Using where; Using temporary |
| 3 | DEPENDENT SUBQUERY | phplist_userid_cache | eq_ref | userid | userid | 3 | phplistdb.phplist_user_user.id | 1 | |
| 3 | DEPENDENT SUBQUERY | geolocation | eq_ref | PRIMARY | PRIMARY | 3 | geolocation.phplist_userid_cache.geolocation__id | 1 | |
| 3 | DEPENDENT SUBQUERY | phplist_listuser | ref | PRIMARY | PRIMARY | 3 | phplistdb.phplist_user_user.id | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | phplist_usermessage | unique_subquery | PRIMARY,messageid | PRIMARY | 8 | func,const | 1 | Using index; Using where |
+----+--------------------+----------------------+-----------------+-------------------------------------+-----------+---------+--------------------------------------------------+---------+---------------------------------------------+
We are planning on moving to InnoDB very soon and I'll pass the recommended query change to dev.
When we switched servers, we upgraded from MySQL 5.1 to 5.5. The data was dumped and reimported to the new MySQL server. Would it be possible that a step I forgot could lead to this sort of problem?
Is it possible that there is some faulty hardware? The only thing that has changed is the MySQL version and the server hardware. The config, and application have not changed and this never used to happen on the old server.
Subject
Views
Written By
Posted
2446
March 03, 2014 05:33PM
1183
March 04, 2014 03:29AM
Re: MySQL 5.5 Queries occasionally extremely slow
1000
March 04, 2014 12:43PM
1065
March 05, 2014 04:37AM
976
March 05, 2014 10:32PM
904
March 10, 2014 02:18PM
1011
March 10, 2014 02:16PM
1172
March 04, 2014 01:24PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.