MySQL Forums
Forum List  »  Performance

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL 5.5 Queries occasionally extremely slow
1000
March 04, 2014 12:43PM


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.