User profile page: very slow query in large UR forums creates temp table, examines 250,000 rows
Hey guys!
We're having an issue with a very slow query on our Ask Dan & Jennifer Forums that have around 8,000 posts (responses). Specifically this query is called by the UseResponse user profile page.
This server runs all our Wordpress installs as well as our UseResponse forums, and this is the 1 frequent query that is the most resource intensive on our system so it's frequently in our MySQL "slow query log". :-)
Here are some examples of the query from our MySQL "slow query log":
http://www.screencast.com/t/6Vpao2tnori
You can see it takes 17 seconds to run on a very fast server, probably because it's examining 250,000 rows every time it runs...
Here is the "MySQL explain" of the query, and the description of the table:
http://www.screencast.com/t/dZuPoE4UnW
You can see in the "MySQL explain" that it's having to create a temp table to run this query, probably because of the number of rows examined...
Would you be able to update the query so it does not require a temp table - and not examine 250,000 rows and maybe make more use of indexes?
In the "MySQL explain" above, it looks like no index is used on the ur_responses table which requires a very large dataset to be returned every time this query is run.
Please advise. :-)
Thanks!
The problem is fixed in 2.3.10 release
The problem is fixed in 2.3.10 release
Dan,
As for now I can suggest you to execute "OPTIMIZE" command on all database tables to rebuild indexes, but it will resolve this issue partially.
We are working on 3.0 release which also includes all perfomance boosts and will be available about 2 months later. All of slow queries will be totally refactored and optimized during preparing 3.0 release.
Thanks for your understanding and waiting.
Dan,
As for now I can suggest you to execute "OPTIMIZE" command on all database tables to rebuild indexes, but it will resolve this issue partially.
We are working on 3.0 release which also includes all perfomance boosts and will be available about 2 months later. All of slow queries will be totally refactored and optimized during preparing 3.0 release.
Thanks for your understanding and waiting.
Hey Paul, thank you for that answer. I optimized the tables in this database and it improved performance a bit (17 seconds down to 13)... but those queries are still a huge drag on our server.
I understand what you're saying about the next version, and I'm very happy you're optimizing the queries in 3.0. We very much look forward to the 3.0 release...
Hey Paul, thank you for that answer. I optimized the tables in this database and it improved performance a bit (17 seconds down to 13)... but those queries are still a huge drag on our server.
I understand what you're saying about the next version, and I'm very happy you're optimizing the queries in 3.0. We very much look forward to the 3.0 release...
+1! We are experiencing huge loads on our servers too. Guys, please hurry!
+1! We are experiencing huge loads on our servers too. Guys, please hurry!
The quickest way here to fix the problem will be removing statistics box on profile page, if it's an option for you. Otherwise, please share FTP details with us or send them to [email protected] for us to investigate the problem
The quickest way here to fix the problem will be removing statistics box on profile page, if it's an option for you. Otherwise, please share FTP details with us or send them to [email protected] for us to investigate the problem
Stas, that's really great news!!
We can certainly edit that file and remove the statistics box - what file specifically should we edit to remove the statistics box?
Thanks!
Stas, that's really great news!!
We can certainly edit that file and remove the statistics box - what file specifically should we edit to remove the statistics box?
Thanks!
The problem is fixed in 2.3.10 release
The problem is fixed in 2.3.10 release
Replies have been locked on this page!